Hierarchical List of All tables

• Mahesh Hello

  Mr or Mrs. 500

  Points: 580

  More actions

  May 23, 2013 at 3:19 pm

  #1618049

  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