May 23, 2013 at 3:19 pm
Lowell (3/23/2011)
ok see if this is even close to what you are looking for;i'm assuming you need to generate the delete statements because you know a certain key must be deleted in some master table.
this generates two levels of deletes: the direct child tables, and potentially grandchildren tables;
SELECT
DeleteID = 1,
RefID = conz.referenced_object_id,
refTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)),
refColumn = ParentColz.name ,
childID = conz.parent_object_id,
childTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id)),
childColumn = ChildColz.name,
cmd = 'DELETE FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id))
+ ' WHERE ' + ChildColz.name + ' IN (SELECT ' + ParentColz.name
+ ' FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)) + ' WHERE ' + ParentColz.name + ' = 0 ) --change to the correct value'
INTO #Delete
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns FKcolz
ON conz.object_id = FKcolz.constraint_object_id
INNER JOIN sys.columns ChildColz
ON FKcolz.parent_object_id = ChildColz.object_id
AND FKcolz.parent_column_id = ChildColz.column_id
INNER JOIN sys.columns ParentColz
ON FKcolz.referenced_object_id = ParentColz.object_id
AND FKcolz.referenced_column_id = ParentColz.column_id
WHERE conz.referenced_object_id = object_id('dbo.GMACT')
--now, potentially, these child table rows cannot be deleted if they, themseleves, have foreign keys.
--add them to the list
INSERT INTO #DELETE
SELECT
DeleteID = 2,
RefID = conz.referenced_object_id,
refTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)),
refColumn = ParentColz.name ,
childID = conz.parent_object_id,
childTable = QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id)),
childColumn = ChildColz.name,
cmd = 'DELETE FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.parent_object_id))
+ ' WHERE ' + ChildColz.name + ' IN (SELECT ' + ParentColz.name
+ ' FROM ' + QUOTENAME(schema_name(conz.schema_id)) + '.' + QUOTENAME(object_name(conz.referenced_object_id)) + ' WHERE ' + ParentColz.name + ' = 0 ) --change to the correct value'
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns FKcolz
ON conz.object_id = FKcolz.constraint_object_id
INNER JOIN sys.columns ChildColz
ON FKcolz.parent_object_id = ChildColz.object_id
AND FKcolz.parent_column_id = ChildColz.column_id
INNER JOIN sys.columns ParentColz
ON FKcolz.referenced_object_id = ParentColz.object_id
AND FKcolz.referenced_column_id = ParentColz.column_id
WHERE conz.referenced_object_id IN (SELECT childID FROM #DELETE)
--how many hierarchys / how deep does the rabbit hole go?
SELECT cmd FROM #DELETE ORDER BY DELETEID DESC
Is it possible to get insert stmt using similar logic?
Like if I want to transfer data (from prod to test env) for a given AccountID from Accounts table.. I should be able to pull all child tables in the hierarchy..
Thanks
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply