Finding Table and Primary Key and Foreign Key

SQL Server does not help to easily determine the dependencies between tables. This research presents the parent table and the child tables and columns used.

https://msdn.microsoft.com/en-us/library/ms179610.aspx http://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server

well, however, we have many databases with bad design that didn’t have foreign keys defined but that did have related data.

 1
 2SELECT
 3      ROW_NUMBER() OVER (ORDER BY ForeignKeyTable.name) as rownumber
 4    , PARENTSchemas.name AS PARENT_SchemaName
 5    , PARENTObject.name AS PARENT_TableName
 6    , PARENTColumn.name AS PARENT_ColumnName
 7    --, PARENTColumn.column_id AS PARENT_ColumnPosition
 8    , ForeignKeySchema.name AS FOREIGNKEY_SchemaName
 9    , ForeignKeyTable.name  AS FOREIGNKEY_TableName
10    , ForeignKeyColumn.name AS FOREIGNKEY_ColumnName
11    --, sys.foreign_key_columns.referenced_object_id AS PARENT_OBJECT_ID
12    --, sys.foreign_key_columns.referenced_column_id AS PARENT_COLUMN_ID
13    --, sys.foreign_key_columns.constraint_object_id
14    --, sys.foreign_key_columns.constraint_column_id
15    --, sys.foreign_key_columns.parent_object_id AS FOREIGNKEY_OBJECT_ID
16    --, sys.foreign_key_columns.parent_column_id AS FOREIGNKEY_COLUMN_ID
17    , sys.foreign_keys.is_disabled
18FROM
19    sys.foreign_key_columns INNER JOIN
20    sys.foreign_keys ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id INNER JOIN
21    sys.objects AS PARENTObject ON sys.foreign_key_columns.referenced_object_id = PARENTObject.object_id INNER JOIN
22    sys.schemas AS PARENTSchemas ON PARENTObject.schema_id = PARENTSchemas.schema_id INNER JOIN
23    sys.columns AS ForeignKeyColumn ON sys.foreign_key_columns.parent_object_id = ForeignKeyColumn.object_id AND
24    sys.foreign_key_columns.parent_column_id = ForeignKeyColumn.column_id INNER JOIN
25    sys.objects AS ForeignKeyTable ON sys.foreign_key_columns.parent_object_id = ForeignKeyTable.object_id INNER JOIN
26    sys.columns AS PARENTColumn ON sys.foreign_key_columns.referenced_object_id = PARENTColumn.object_id AND
27    sys.foreign_key_columns.referenced_column_id = PARENTColumn.column_id INNER JOIN
28    sys.schemas AS ForeignKeySchema ON ForeignKeyTable.schema_id = ForeignKeySchema.schema_id
29WHERE (PARENTObject.type = 'U')

This example of use is not a perfect solution. Considerations and compromises that limit this solution:

* Tables can not have self-reference.

* The selection conditions are repeated and are not an example of performance. I thought to use temporary tables but I do not have time and patience.

The SQL code is created to be used to create an initial version.

  1
  2DROP PROCEDURE sDBACascateDelete
  3Go
  4CREATE PROCEDURE sDBACascateDelete
  5(
  6   @schemaname nvarchar(100)
  7 , @tablename nvarchar(100)
  8 , @where nvarchar(1024)
  9 , @recursive int = 1
 10
 11)
 12AS
 13BEGIN
 14
 15SET NOCOUNT ON
 16
 17DECLARE
 18   @schemanameNEW nvarchar(100)
 19 , @tablenameNEW nvarchar(100)
 20 , @whereNEW nvarchar(1024)
 21DECLARE @SQL nvarchar(max);
 22DECLARE @SQL1 nvarchar(max);
 23DECLARE @SQLTODelete nvarchar(max);
 24DECLARE @rownumber INT;
 25
 26DECLARE @TMP TABLE (
 27    [rownumber] [bigint] NULL,
 28    [PARENT_SchemaName] [sysname] NOT NULL,
 29    [PARENT_TableName] [sysname] NOT NULL,
 30    [PARENT_ColumnName] [sysname] NULL,
 31    [PARENT_ColumnPosition] [int] NOT NULL,
 32    [FOREIGNKEY_SchemaName] [sysname] NOT NULL,
 33    [FOREIGNKEY_TableName] [sysname] NOT NULL,
 34    [FOREIGNKEY_ColumnName] [sysname] NULL,
 35    [PARENT_OBJECT_ID] [int] NOT NULL,
 36    [PARENT_COLUMN_ID] [int] NOT NULL,
 37    [constraint_object_id] [int] NOT NULL,
 38    [constraint_column_id] [int] NOT NULL,
 39    [FOREIGNKEY_OBJECT_ID] [int] NOT NULL,
 40    [FOREIGNKEY_COLUMN_ID] [int] NOT NULL,
 41    [is_disabled] [bit] NOT NULL
 42);
 43
 44SET @SQL = char(13)+char(10);
 45SET @SQL = '';
 46SET @SQL = 'DELETE FROM ['+@schemaname+'].['+@tablename+'] WHERE '+@where+@SQL + ';';
 47SET @SQLTODelete = ' FROM ['+@schemaname+'].['+@tablename+'] WHERE '+@where;
 48
 49INSERT INTO @TMP
 50SELECT
 51      ROW_NUMBER() OVER (ORDER BY ForeignKeyTable.name) as rownumber
 52    , PARENTSchemas.name AS PARENT_SchemaName
 53    , PARENTObject.name AS PARENT_TableName
 54    , PARENTColumn.name AS PARENT_ColumnName
 55    , PARENTColumn.column_id AS PARENT_ColumnPosition
 56    , ForeignKeySchema.name AS FOREIGNKEY_SchemaName
 57    , ForeignKeyTable.name  AS FOREIGNKEY_TableName
 58    , ForeignKeyColumn.name AS FOREIGNKEY_ColumnName
 59    , sys.foreign_key_columns.referenced_object_id AS PARENT_OBJECT_ID
 60    , sys.foreign_key_columns.referenced_column_id AS PARENT_COLUMN_ID
 61    , sys.foreign_key_columns.constraint_object_id
 62    , sys.foreign_key_columns.constraint_column_id
 63    , sys.foreign_key_columns.parent_object_id AS FOREIGNKEY_OBJECT_ID
 64    , sys.foreign_key_columns.parent_column_id AS FOREIGNKEY_COLUMN_ID
 65    , sys.foreign_keys.is_disabled
 66FROM
 67    sys.foreign_key_columns INNER JOIN
 68    sys.foreign_keys ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id INNER JOIN
 69    sys.objects AS PARENTObject ON sys.foreign_key_columns.referenced_object_id = PARENTObject.object_id INNER JOIN
 70    sys.schemas AS PARENTSchemas ON PARENTObject.schema_id = PARENTSchemas.schema_id INNER JOIN
 71    sys.columns AS ForeignKeyColumn ON sys.foreign_key_columns.parent_object_id = ForeignKeyColumn.object_id AND
 72    sys.foreign_key_columns.parent_column_id = ForeignKeyColumn.column_id INNER JOIN
 73    sys.objects AS ForeignKeyTable ON sys.foreign_key_columns.parent_object_id = ForeignKeyTable.object_id INNER JOIN
 74    sys.columns AS PARENTColumn ON sys.foreign_key_columns.referenced_object_id = PARENTColumn.object_id AND
 75    sys.foreign_key_columns.referenced_column_id = PARENTColumn.column_id INNER JOIN
 76    sys.schemas AS ForeignKeySchema ON ForeignKeyTable.schema_id = ForeignKeySchema.schema_id
 77WHERE (PARENTObject.type = 'U')
 78AND PARENTSchemas.name = @schemaname
 79AND PARENTObject.name  = @tablename
 80
 81-- select * from @TMP
 82
 83DELETE FROM @TMP WHERE FOREIGNKEY_SchemaName = @tablename AND FOREIGNKEY_TableName =  @schemaname;
 84--SELECT * FROM #TMP;
 85
 86DECLARE @n INT = 26;
 87
 88DECLARE  @PARENT_tableName AS NVARCHAR(MAX);
 89WHILE (@recursive=1 AND (SELECT count(*) FROM @TMP)>0)
 90BEGIN
 91
 92    ----SELECT TOP 1 @PARENT_tableName = PARENT_tableName, @sql1 = 'DELETE FROM ' + PARENT_tableName FROM #TMP WHERE FOREIGNKEY_TableName = @tablename AND FOREIGNKEY_SchemaName = @schemaname;
 93    ----SELECT TOP 1 * FROM #TMP WHERE FOREIGNKEY_TableName = @tablename AND FOREIGNKEY_SchemaName = @schemaname;
 94    --SELECT @sql1 = 'DELETE FROM ['+FOREIGNKEY_SchemaName+'].['+FOREIGNKEY_TableName+'] WHERE ' + FOREIGNKEY_ColumnName + ' in (SELECT '+PARENT_ColumnName+' FROM ['+PARENT_SchemaName+'].['+PARENT_TableName+'])'
 95    --FROM #TMP WHERE PARENT_TableName = @tablename AND PARENT_SchemaName = @schemaname;
 96    --PRINT @sql1;
 97
 98    SELECT TOP 1
 99            @rownumber  = rownumber
100          , @schemanameNEW = FOREIGNKEY_SchemaName
101          , @tablenameNEW  = FOREIGNKEY_TableName
102          , @whereNEW      = FOREIGNKEY_ColumnName + ' in (SELECT '+PARENT_ColumnName + @SQLTODelete + ')'
103    FROM @TMP
104    --SELECT count(*) as n FROM @TMP;
105    DELETE FROM @TMP WHERE rownumber=@rownumber;
106    --SELECT count(*) as n FROM @TMP;
107
108    --PRINT @rownumber;
109    --print @schemaname
110    --print @tablename
111    --print @where
112    IF (@tablename = @tablenameNEW)
113        exec sDBACascateDelete @schemanameNEW, @tablenameNEW, @whereNEW, 0
114    else
115        exec sDBACascateDelete @schemanameNEW, @tablenameNEW, @whereNEW, 1
116
117    -- DELETE FROM #TMP WHERE  FOREIGNKEY_TableName = @tablename AND FOREIGNKEY_SchemaName = @schemaname;
118    IF (@n=0) break;
119    SET @n = @n - 1;
120END
121
122PRINT @sql
123
124END
125GO

Ideal use :

1
2exec sDBACascateDelete @schemaname='dbo', @tablename='Region', @where='EmployeesID < @EmployeesID'
3
4/** RESULT **/
5
6DELETE FROM [dbo].[EmployeeTerritories] WHERE TerritoryID in (SELECT TerritoryID FROM [dbo].[Territories] WHERE RegionID in (SELECT RegionID FROM [dbo].[Region] WHERE EmployeesID < @EmployeesID));
7DELETE FROM [dbo].[Territories] WHERE RegionID in (SELECT RegionID FROM [dbo].[Region] WHERE EmployeesID < @EmployeesID);
8DELETE FROM [dbo].[Region] WHERE EmployeesID < @EmployeesID;

This example the table dbo.[Employees] has a self-reference. In this case the code should be rewritten because it certainly will not work.

1
2exec sDBACascateDelete @schemaname='dbo', @tablename='Employees', @where='EmployeesID < @EmployeesID'
3
4/* RESULT */
5DELETE FROM [dbo].[Employees] WHERE ReportsTo in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID);
6DELETE FROM [dbo].[EmployeeTerritories] WHERE EmployeeID in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID);
7DELETE FROM [dbo].[Order Details] WHERE OrderID in (SELECT OrderID FROM [dbo].[Orders] WHERE EmployeeID in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID));
8DELETE FROM [dbo].[Orders] WHERE EmployeeID in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID);
9DELETE FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID;

** I use NorthWind database **

Finding Table and Primary Key and Foreign Key

Contents

Copyright

Comments

Finding Table and Primary Key and Foreign Key

Contents

Copyright

Related Posts

Comments