http://www.sqlmag.com/authors/author/author/5777876/rsscomment/5777876en-USFri, 25 May 2012 09:29:19 GMTFri, 25 May 2012 09:29:19 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorFri, 19 Jun 2009 15:45:09 GMT
I propose the following code which makes consistent use on SQL Server 2005 and above system views (using sys.foreign_keys instead of sysreferences, kept in 2005+ only for backward compatibility reasons): select SCHEMA_NAME(fk.schema_id) AS FSC_NAME, /*OBJECT_NAME(fk.parent_object_ID) FOName,*/ fk.parent_object_id FKEYID, SCHEMA_NAME(o.schema_id) AS RSC_NAME, /*OBJECT_NAME(fk.referenced_object_id) AS ROName,*/ fk.referenced_object_id AS RKEYID into #sysref from sys.foreign_keys FK join sys.objects o on FK.referenced_object_id=o.object_id and not exists (select 1 from sys.foreign_keys FK1 where FK.parent_object_id=FK1.referenced_object_id and FK.referenced_object_id=FK1.parent_object_id)]]>GabrielaFri, 19 Jun 2009 15:45:09 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorFri, 19 Jun 2009 15:44:48 GMT
Possible problem: This condition might create problems if the same table name exists in more than one schema: OBJECT_NAME(T1.FKEYID)+OBJECT_NAME(T1.RKEYID) = OBJECT_NAME(T2.RKEYID)+OBJECT_NAME(T2.FKEYID) Function OBJECT_NAME() returns the database object name, but it gives no information about the schema. In this particular scenario: Schema1.Tab1 references Schema1.Tab2 Schema1.Tab2 references Schema2.Tab1 the above code will exclude it as a false circular reference. However, these are two perfectly valid relationships to be included in temporary table #sysref.]]>GabrielaFri, 19 Jun 2009 15:44:48 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorFri, 19 Jun 2009 15:43:54 GMT
I love the recursive CTE idea! This is a very ingenious way to get the list of tables with references in a proper order for DELETE or INSERT! However, I have a question and a few comments which I will try to address in a few successive posts – due to limited # of characters allowed by the site in one message. Regarding the SELECT used to create the first temporary table (#sysref): Question: Why using left outer join to link with sys.objects (SOF, SOR)? I think a simple INNER JOIN will have sufficed.]]>GabrielaFri, 19 Jun 2009 15:43:54 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorMon, 15 Jun 2009 12:33:28 GMT
this is solved my problem. earlier we used to generate the script to release into production,but the inbuilt script option in ssms doesnt generates the database diagrams from devolopment to production. we have created a new script based on this script to make an automated job to release from devolopment to UAT for testing purpose.. nice work..]]>manishMon, 15 Jun 2009 12:33:28 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorMon, 15 Jun 2009 11:56:50 GMT
This article is for how to identify the hierarchies to delete and insert the data using SSIS or during any refresh automations. For restoring only schema : Yes,ssms script task is available to create the scripts and its a manual process where the DBA has to select the objects which are required to release. there might be a chance to miss any single object and he has to follow a process to ensure that both devolopment and production are in sync once he released with scripts. But in this process the production will get exact replica of devolopment database with out any mismatch.]]>chandraMon, 15 Jun 2009 11:56:50 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorMon, 15 Jun 2009 08:35:55 GMT
I use to create the script of the database and execute it on the new server. This can be done without any code directly from SSMS. Take Care.]]>MarcosMon, 15 Jun 2009 08:35:55 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorThu, 11 Jun 2009 04:31:36 GMT
Great article and interesting solution. I downloaded the code and will look at it latter on. In some environments you will have to disable all triggers in addition to FKs. I would like to share with you a freeware I published which automates the process of schema generation. It may match your needs. http://blogs.microsoft.co.il/blogs/yaniv_etrogi/SQLScripter/SQLScripter.zip]]>YanivThu, 11 Jun 2009 04:31:36 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchorThu, 21 May 2009 11:18:29 GMT
I was looking for a solution like this!]]>LucaThu, 21 May 2009 11:18:29 GMThttp://www.sqlmag.com/article/tsql3/avoid-referential-integrity-errors-when-deleting-records-from-databases#commentsAnchor