Table Dependency Computation + Foreign-Key Diagnostics

DevViews.sql

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DELDATA_CreateForeignKeyRelations]'))
DROP VIEW [dbo].[V_DELDATA_CreateForeignKeyRelations]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DELDATA_Tables_All]'))
DROP VIEW [dbo].[V_DELDATA_Tables_All]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_FindMissingForeignKeys]'))
DROP VIEW [dbo].[V_DEV_FindMissingForeignKeys]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_ForeignKeyRelations]'))
DROP VIEW [dbo].[V_DEV_ForeignKeyRelations]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_PrimaryKeyColumns]'))
DROP VIEW [dbo].[V_DEV_PrimaryKeyColumns]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_Tables_without_PrimaryKey]'))
DROP VIEW [dbo].[V_DEV_Tables_without_PrimaryKey]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_TableSizeInfo]'))
DROP VIEW [dbo].[V_DEV_TableSizeInfo]
GO


IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_TableSizeInfo]'))
EXEC dbo.sp_executesql @statement = N'


CREATE VIEW [dbo].[V_DEV_TableSizeInfo] 
AS 
SELECT TOP 999999999
     TableProperties.name AS TableName 
    ,PartitionProperties.rows AS RowCounts 
    ,SUM(AllocUnits.total_pages) * 8 AS TotalSpaceKB 
    ,SUM(AllocUnits.used_pages) * 8 AS UsedSpaceKB 
    ,(SUM(AllocUnits.total_pages) - SUM(AllocUnits.used_pages)) * 8 AS UnusedSpaceKB 
FROM sys.tables AS TableProperties 

INNER JOIN sys.indexes AS Indices 
	ON TableProperties.OBJECT_ID = Indices.object_id 
	
INNER JOIN sys.partitions AS PartitionProperties 
	--ON PartitionProperties.OBJECT_ID = TableProperties.object_id 
	ON PartitionProperties.OBJECT_ID = Indices.object_id 
	AND Indices.index_id = PartitionProperties.index_id 
	
INNER JOIN sys.allocation_units AS AllocUnits 
	ON PartitionProperties.partition_id = AllocUnits.container_id 
	
WHERE 
    TableProperties.name NOT LIKE ''dt%'' 
    AND TableProperties.is_ms_shipped = 0 
    --AND Indices.OBJECT_ID > 255 
    
GROUP BY 
    TableProperties.name, PartitionProperties.Rows 
    --TableProperties.is_ms_shipped -- Group for entire table 
    
ORDER BY 
	 TotalSpaceKB DESC 
	,TableName 
   
'
GO


IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_Tables_without_PrimaryKey]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[V_DEV_Tables_without_PrimaryKey] 
AS
WITH Tables_without_PrimaryKey AS 
(
	SELECT TOP 100 PERCENT 
		 --SCHEMA_NAME(schema_id) AS SchemaName
		 name AS TableName
		,
		(
			SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE TABLE_NAME = name 
			AND ORDINAL_POSITION = 1 
		) AS COLUMN_NAME
		,
		(
			SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE TABLE_NAME = name 
			AND ORDINAL_POSITION = 1 
		) AS DATA_TYPE
		,
		(
			SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE TABLE_NAME = name 
			AND ORDINAL_POSITION = 1 
		) AS CHARACTER_MAXIMUM_LENGTH
		
	FROM sys.tables
	WHERE OBJECTPROPERTY(OBJECT_ID,''TableHasPrimaryKey'') = 0
	--ORDER BY SchemaName, TableName;
	ORDER BY TableName
)

SELECT 
	''ALTER TABLE ['' + TableName + ''] ALTER COLUMN ['' + COLUMN_NAME + ''] '' + DATA_TYPE + '' NOT NULL;'' as xxx
FROM Tables_without_PrimaryKey

UNION ALL 

SELECT ''''

UNION ALL 

SELECT 
	''ALTER TABLE ['' + TableName + ''] ADD CONSTRAINT [PK_'' + TableName + ''] PRIMARY KEY (['' + COLUMN_NAME + '']);''	
FROM Tables_without_PrimaryKey

'
GO



IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_PrimaryKeyColumns]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[V_DEV_PrimaryKeyColumns]  
AS 
SELECT 
	 ccu.TABLE_SCHEMA
	,ccu.TABLE_NAME
	,ccu.COLUMN_NAME
	,ccu.CONSTRAINT_NAME
	,ic.ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
	ON tc.CONSTRAINT_NAME = ccu.Constraint_name 
	
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS ic
	ON ic.TABLE_SCHEMA = ccu.TABLE_SCHEMA
	AND ic.TABLE_NAME = ccu.TABLE_NAME
	AND ic.COLUMN_NAME = ccu.COLUMN_NAME 
	
WHERE tc.CONSTRAINT_TYPE = ''PRIMARY KEY''


--TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
--COR_Basic	dbo	T_ZO_AP_Kontakte_AP_Ref_Kostenstelle

--ORDER BY TABLE_NAME, COLUMN_NAME 
'
GO



IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_ForeignKeyRelations]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[V_DEV_ForeignKeyRelations] 
AS 
SELECT  
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
	,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA 
	,KCU1.TABLE_NAME AS FK_TABLE_NAME 
	,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
	,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
	,RC.DELETE_RULE AS FK_DELETE_RULE
	,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
	,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA 
	,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
	,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
	,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
	ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
	AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
	AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
	
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
	ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
	AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
	AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
	AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
'
GO




IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DEV_FindMissingForeignKeys]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[V_DEV_FindMissingForeignKeys] 
AS 
SELECT TOP 9999999999 
	 ic.TABLE_SCHEMA 
	,ic.TABLE_NAME 
	,ic.COLUMN_NAME 
	,ic.ORDINAL_POSITION 
	,ic.COLUMN_DEFAULT 
	,ic.IS_NULLABLE 
	,ic.DATA_TYPE 
	,ic.CHARACTER_MAXIMUM_LENGTH 
	,ic.CHARACTER_OCTET_LENGTH 
	,ic.NUMERIC_PRECISION 
	,ic.NUMERIC_PRECISION_RADIX 
	,ic.NUMERIC_SCALE 
	,ic.DATETIME_PRECISION 
FROM INFORMATION_SCHEMA.COLUMNS AS ic 

LEFT JOIN INFORMATION_SCHEMA.TABLES AS it 
	ON it.TABLE_NAME = ic.TABLE_NAME 
	AND it.TABLE_SCHEMA = ic.TABLE_SCHEMA 
	AND it.TABLE_CATALOG = ic.TABLE_CATALOG 
	
WHERE (1=1) 
AND it.TABLE_TYPE = ''BASE TABLE'' 
-- AND ic.COLUMN_NAME LIKE ''%KT_UID'' 

ORDER BY ic.TABLE_NAME, ic.ORDINAL_POSITION 
'
GO




IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DELDATA_Tables_All]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[V_DELDATA_Tables_All] 
AS
WITH Fkeys AS (

    SELECT DISTINCT 
         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name 
    FROM sysforeignkeys fk 
        
        INNER JOIN sysobjects onTable 
            ON fk.fkeyid = onTable.id 
            
        INNER JOIN sysobjects againstTable  
            ON fk.rkeyid = againstTable.id 
            
    WHERE 1=1
        AND AgainstTable.TYPE = ''U''
        AND OnTable.TYPE = ''U''
        -- ignore self joins; they cause an infinite recursion
        AND OnTable.Name <> AgainstTable.Name
    )

,MyData AS (

    SELECT 
         OnTable = o.name 
        ,AgainstTable = FKeys.againstTable 
    FROM sys.objects o 
        
    LEFT JOIN FKeys
		ON o.name = FKeys.onTable 
		
    WHERE (1=1) 
        AND o.type = ''U'' 
        AND o.name NOT LIKE ''sys%'' 
    )

,MyRecursion AS (

    -- base case
    SELECT  
         TableName    = OnTable
        ,Lvl        = 1
    FROM MyData
    WHERE 1=1
        AND AgainstTable IS NULL 
        
    -- recursive case
    UNION ALL 
    
    SELECT 
         TableName = OnTable 
        ,Lvl       = r.Lvl + 1 
    FROM MyData d 
        INNER JOIN MyRecursion r 
            ON d.AgainstTable = r.TableName 
)


SELECT TOP 999999999999999999 
     Lvl = max(Lvl)
    ,TableName 
    ,''DELETE FROM ['' + REPLACE(TableName, '''''''', '''''''''''') + '']; '' AS DeleteCmd 
FROM 
    MyRecursion

GROUP BY
    TableName
    
    
ORDER BY lvl DESC, TableName
'
GO



IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_DELDATA_CreateForeignKeyRelations]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[V_DELDATA_CreateForeignKeyRelations] 
AS 
SELECT 
	 FK_CONSTRAINT_NAME
	,FK_TABLE_NAME
	,FK_COLUMN_NAME
	,FK_ORDINAL_POSITION
	,FK_DELETE_RULE
	,REFERENCED_CONSTRAINT_NAME
	,REFERENCED_TABLE_NAME
	,REFERENCED_COLUMN_NAME
	,REFERENCED_ORDINAL_POSITION
      
	,''
      
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''''[dbo].'' + QUOTENAME(FK_CONSTRAINT_NAME) + '''''') AND parent_object_id = OBJECT_ID(N''''[dbo].'' + QUOTENAME(FK_TABLE_NAME) + '''''')) 
ALTER TABLE [dbo].'' + QUOTENAME(FK_TABLE_NAME) + '' DROP CONSTRAINT '' + QUOTENAME(FK_CONSTRAINT_NAME) + ''
      
      
ALTER TABLE [dbo].'' + QUOTENAME(FK_TABLE_NAME) + '' WITH CHECK ADD  CONSTRAINT '' + QUOTENAME(FK_CONSTRAINT_NAME) + '' FOREIGN KEY('' + QUOTENAME(FK_COLUMN_NAME) + '')
REFERENCES [dbo].'' + QUOTENAME(REFERENCED_TABLE_NAME) + '' ('' + QUOTENAME(REFERENCED_COLUMN_NAME) + '')
'' + CASE WHEN FK_DELETE_RULE = ''CASCADE'' 
		THEN ''ON DELETE CASCADE ''
		ELSE ''''
	END + ''
;
'' AS cmdCreateFK    
FROM V_DEV_ForeignKeyRelations 
'
GO