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
September 13, 2024 at 5:16 pm
Gad. Zukes.
I have looked for something like this for so long, and just stumbled across it in your answer today. My mouth is literally agape.
Bless you, sir. Bless you.
September 14, 2024 at 11:40 am
still based on the same old code from ten years ago, this is what i do today: it's just a handfull of legos/snippets, assembled into the shape of something i need
a temp table, sp_MSdependancies, and then a loop to lookup more detailed information for me to use.
you could modify that to have a column for a dynamically build DELETE/TRUNCATE
--desc: creates drop object statements in hierarchy order
IF OBJECT_ID('tempdb..[#MyObjectHierarchy]') IS NOT NULL
DROP TABLE [#MyObjectHierarchy]
CREATE TABLE [#MyObjectHierarchy]
(
[HID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[ObjectID] INT,
[SchemaName] VARCHAR(255),
[ObjectName] VARCHAR(255),
[ObjectType] VARCHAR(255),
[oTYPE] INT,
[SequenceOrder] INT
);
--our list of objects in dependancy order
INSERT [#MyObjectHierarchy] ([oTYPE],[ObjectName],[SchemaName],[SequenceOrder])
EXEC [sys].[sp_MSdependencies] @intrans = 1;
UPDATE [MyTarget]
SET [MyTarget].[ObjectID] = [objz].[object_id],
[MyTarget].[ObjectType] = [objz].[type_desc]
FROM [#MyObjectHierarchy] AS [MyTarget]
INNER JOIN [sys].[objects] AS [objz]
ON [MyTarget].[ObjectName] COLLATE SQL_Latin1_General_CP1_CI_AS = [objz].[name] COLLATE SQL_Latin1_General_CP1_CI_AS
AND [MyTarget].[SchemaName] COLLATE SQL_Latin1_General_CP1_CI_AS = SCHEMA_NAME([objz].[schema_id]) COLLATE SQL_Latin1_General_CP1_CI_AS;
SELECT *
,' IF EXISTS(SELECT * FROM sys.objects WHERE name = ''' + [ObjectName] + ''') DROP '
+ CASE
WHEN [ObjectType] = 'SQL_STORED_PROCEDURE' THEN ' PROCEDURE '
WHEN [ObjectType] = 'VIEW' THEN ' VIEW '
WHEN [ObjectType] = 'SQL_SCALAR_FUNCTION' THEN ' FUNCTION '
WHEN [ObjectType] = 'SQL_TABLE_VALUED_FUNCTION' THEN ' FUNCTION '
WHEN [ObjectType] = 'SQL_INLINE_TABLE_VALUED_FUNCTION' THEN ' FUNCTION '
WHEN [ObjectType] = 'SERVICE_QUEUE' THEN ' SERVICE ' + [ObjectName]+';DROP QUEUE '
WHEN [ObjectType] = 'USER_TABLE' THEN ' TABLE '
ELSE '??'
END
+ QUOTENAME([SchemaName]) + '.' + QUOTENAME([ObjectName]) + ';'
FROM [#MyObjectHierarchy]
WHERE 1=1
--AND [ObjectType] <> 'SQL_TRIGGER'
--AND [ObjectName] LIKE '%WATCH%'
ORDER BY [HID] DESC; -- drop order
--ORDER BY [HID] ; -- create order
Lowell
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply