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.

SELECT        
      ROW_NUMBER() OVER (ORDER BY ForeignKeyTable.name) as rownumber
    , PARENTSchemas.name AS PARENT_SchemaName
    , PARENTObject.name AS PARENT_TableName
    , PARENTColumn.name AS PARENT_ColumnName
    --, PARENTColumn.column_id AS PARENT_ColumnPosition
    , ForeignKeySchema.name AS FOREIGNKEY_SchemaName
    , ForeignKeyTable.name  AS FOREIGNKEY_TableName
    , ForeignKeyColumn.name AS FOREIGNKEY_ColumnName
    --, sys.foreign_key_columns.referenced_object_id AS PARENT_OBJECT_ID
    --, sys.foreign_key_columns.referenced_column_id AS PARENT_COLUMN_ID
    --, sys.foreign_key_columns.constraint_object_id
    --, sys.foreign_key_columns.constraint_column_id
    --, sys.foreign_key_columns.parent_object_id AS FOREIGNKEY_OBJECT_ID
    --, sys.foreign_key_columns.parent_column_id AS FOREIGNKEY_COLUMN_ID
    , sys.foreign_keys.is_disabled
FROM            
    sys.foreign_key_columns INNER JOIN
    sys.foreign_keys ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id INNER JOIN
    sys.objects AS PARENTObject ON sys.foreign_key_columns.referenced_object_id = PARENTObject.object_id INNER JOIN
    sys.schemas AS PARENTSchemas ON PARENTObject.schema_id = PARENTSchemas.schema_id INNER JOIN
    sys.columns AS ForeignKeyColumn ON sys.foreign_key_columns.parent_object_id = ForeignKeyColumn.object_id AND
    sys.foreign_key_columns.parent_column_id = ForeignKeyColumn.column_id INNER JOIN
    sys.objects AS ForeignKeyTable ON sys.foreign_key_columns.parent_object_id = ForeignKeyTable.object_id INNER JOIN
    sys.columns AS PARENTColumn ON sys.foreign_key_columns.referenced_object_id = PARENTColumn.object_id AND
    sys.foreign_key_columns.referenced_column_id = PARENTColumn.column_id INNER JOIN
    sys.schemas AS ForeignKeySchema ON ForeignKeyTable.schema_id = ForeignKeySchema.schema_id
WHERE (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.

DROP PROCEDURE sDBACascateDelete
Go
CREATE PROCEDURE sDBACascateDelete
(
   @schemaname nvarchar(100)
 , @tablename nvarchar(100)
 , @where nvarchar(1024)
 , @recursive int = 1

)
AS
BEGIN

SET NOCOUNT ON

DECLARE
   @schemanameNEW nvarchar(100)
 , @tablenameNEW nvarchar(100)
 , @whereNEW nvarchar(1024)
DECLARE @SQL nvarchar(max);
DECLARE @SQL1 nvarchar(max);
DECLARE @SQLTODelete nvarchar(max);
DECLARE @rownumber INT;


DECLARE @TMP TABLE (
    [rownumber] [bigint] NULL,
    [PARENT_SchemaName] [sysname] NOT NULL,
    [PARENT_TableName] [sysname] NOT NULL,
    [PARENT_ColumnName] [sysname] NULL,
    [PARENT_ColumnPosition] [int] NOT NULL,
    [FOREIGNKEY_SchemaName] [sysname] NOT NULL,
    [FOREIGNKEY_TableName] [sysname] NOT NULL,
    [FOREIGNKEY_ColumnName] [sysname] NULL,
    [PARENT_OBJECT_ID] [int] NOT NULL,
    [PARENT_COLUMN_ID] [int] NOT NULL,
    [constraint_object_id] [int] NOT NULL,
    [constraint_column_id] [int] NOT NULL,
    [FOREIGNKEY_OBJECT_ID] [int] NOT NULL,
    [FOREIGNKEY_COLUMN_ID] [int] NOT NULL,
    [is_disabled] [bit] NOT NULL
);

SET @SQL = char(13)+char(10);
SET @SQL = '';
SET @SQL = 'DELETE FROM ['+@schemaname+'].['+@tablename+'] WHERE '+@where+@SQL + ';';
SET @SQLTODelete = ' FROM ['+@schemaname+'].['+@tablename+'] WHERE '+@where;  

INSERT INTO @TMP
SELECT        
      ROW_NUMBER() OVER (ORDER BY ForeignKeyTable.name) as rownumber
    , PARENTSchemas.name AS PARENT_SchemaName
    , PARENTObject.name AS PARENT_TableName
    , PARENTColumn.name AS PARENT_ColumnName
    , PARENTColumn.column_id AS PARENT_ColumnPosition
    , ForeignKeySchema.name AS FOREIGNKEY_SchemaName
    , ForeignKeyTable.name  AS FOREIGNKEY_TableName
    , ForeignKeyColumn.name AS FOREIGNKEY_ColumnName
    , sys.foreign_key_columns.referenced_object_id AS PARENT_OBJECT_ID
    , sys.foreign_key_columns.referenced_column_id AS PARENT_COLUMN_ID
    , sys.foreign_key_columns.constraint_object_id
    , sys.foreign_key_columns.constraint_column_id
    , sys.foreign_key_columns.parent_object_id AS FOREIGNKEY_OBJECT_ID
    , sys.foreign_key_columns.parent_column_id AS FOREIGNKEY_COLUMN_ID
    , sys.foreign_keys.is_disabled
FROM            
    sys.foreign_key_columns INNER JOIN
    sys.foreign_keys ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id INNER JOIN
    sys.objects AS PARENTObject ON sys.foreign_key_columns.referenced_object_id = PARENTObject.object_id INNER JOIN
    sys.schemas AS PARENTSchemas ON PARENTObject.schema_id = PARENTSchemas.schema_id INNER JOIN
    sys.columns AS ForeignKeyColumn ON sys.foreign_key_columns.parent_object_id = ForeignKeyColumn.object_id AND
    sys.foreign_key_columns.parent_column_id = ForeignKeyColumn.column_id INNER JOIN
    sys.objects AS ForeignKeyTable ON sys.foreign_key_columns.parent_object_id = ForeignKeyTable.object_id INNER JOIN
    sys.columns AS PARENTColumn ON sys.foreign_key_columns.referenced_object_id = PARENTColumn.object_id AND
    sys.foreign_key_columns.referenced_column_id = PARENTColumn.column_id INNER JOIN
    sys.schemas AS ForeignKeySchema ON ForeignKeyTable.schema_id = ForeignKeySchema.schema_id
WHERE (PARENTObject.type = 'U')
AND PARENTSchemas.name = @schemaname
AND PARENTObject.name  = @tablename

-- select * from @TMP

DELETE FROM @TMP WHERE FOREIGNKEY_SchemaName = @tablename AND FOREIGNKEY_TableName =  @schemaname;
--SELECT * FROM #TMP;

DECLARE @n INT = 26;

DECLARE  @PARENT_tableName AS NVARCHAR(MAX);
WHILE (@recursive=1 AND (SELECT count(*) FROM @TMP)>0)
BEGIN
    
    ----SELECT TOP 1 @PARENT_tableName = PARENT_tableName, @sql1 = 'DELETE FROM ' + PARENT_tableName FROM #TMP WHERE FOREIGNKEY_TableName = @tablename AND FOREIGNKEY_SchemaName = @schemaname;
    ----SELECT TOP 1 * FROM #TMP WHERE FOREIGNKEY_TableName = @tablename AND FOREIGNKEY_SchemaName = @schemaname;
    --SELECT @sql1 = 'DELETE FROM ['+FOREIGNKEY_SchemaName+'].['+FOREIGNKEY_TableName+'] WHERE ' + FOREIGNKEY_ColumnName + ' in (SELECT '+PARENT_ColumnName+' FROM ['+PARENT_SchemaName+'].['+PARENT_TableName+'])'
    --FROM #TMP WHERE PARENT_TableName = @tablename AND PARENT_SchemaName = @schemaname;
    --PRINT @sql1;

    SELECT TOP 1
            @rownumber  = rownumber
          , @schemanameNEW = FOREIGNKEY_SchemaName
          , @tablenameNEW  = FOREIGNKEY_TableName
          , @whereNEW      = FOREIGNKEY_ColumnName + ' in (SELECT '+PARENT_ColumnName + @SQLTODelete + ')'
    FROM @TMP
    --SELECT count(*) as n FROM @TMP;
    DELETE FROM @TMP WHERE rownumber=@rownumber;
    --SELECT count(*) as n FROM @TMP;

    --PRINT @rownumber;
    --print @schemaname
    --print @tablename
    --print @where
    IF (@tablename = @tablenameNEW)
        exec sDBACascateDelete @schemanameNEW, @tablenameNEW, @whereNEW, 0
    else
        exec sDBACascateDelete @schemanameNEW, @tablenameNEW, @whereNEW, 1

    -- DELETE FROM #TMP WHERE  FOREIGNKEY_TableName = @tablename AND FOREIGNKEY_SchemaName = @schemaname;
    IF (@n=0) break;
    SET @n = @n - 1;
END

PRINT @sql

END
GO

Ideal use :

exec sDBACascateDelete @schemaname='dbo', @tablename='Region', @where='EmployeesID < @EmployeesID'
 

/** RESULT **/

DELETE FROM [dbo].[EmployeeTerritories] WHERE TerritoryID in (SELECT TerritoryID FROM [dbo].[Territories] WHERE RegionID in (SELECT RegionID FROM [dbo].[Region] WHERE EmployeesID < @EmployeesID));
DELETE FROM [dbo].[Territories] WHERE RegionID in (SELECT RegionID FROM [dbo].[Region] WHERE EmployeesID < @EmployeesID);
DELETE 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.

exec sDBACascateDelete @schemaname='dbo', @tablename='Employees', @where='EmployeesID < @EmployeesID'

/* RESULT */
DELETE FROM [dbo].[Employees] WHERE ReportsTo in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID);
DELETE FROM [dbo].[EmployeeTerritories] WHERE EmployeeID in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID);
DELETE FROM [dbo].[Order Details] WHERE OrderID in (SELECT OrderID FROM [dbo].[Orders] WHERE EmployeeID in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID));
DELETE FROM [dbo].[Orders] WHERE EmployeeID in (SELECT EmployeeID FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID);
DELETE FROM [dbo].[Employees] WHERE EmployeesID < @EmployeesID;

** I use NorthWind database **