Uncategorized

List duplicated foreign key relations

Transact-SQL ;WITH [Basic] AS ( SELECT [fk].[parent_object_id], [fkc].[parent_column_id], [fk].[referenced_object_id], [fkc].[referenced_column_id], COUNT(*) [Cnt] FROM [sys].[foreign_keys] [fk] INNER JOIN [sys].[foreign_key_columns] [fkc] ON [fkc].[constraint_object_id] = [fk].[object_id] GROUP BY [fk].[parent_object_id], [fkc].[parent_column_id], [fk].[referenced_object_id], [fkc].[referenced_column_id] HAVING COUNT(*) > 1 ), Dupl AS ( SELECT [fk].[name] [FKName], [s1].[name] [ParentSchema], [t1].[name] [ParentTable], [ParentColumn] = [c1].[name], [ReferencedTable] = [t2].[name], [ReferencedColumn] = [c2].[name] FROM [sys].[foreign_keys] [fk] INNER JOIN [sys].[foreign_key_columns] [fkc] ON [fkc].[constraint_object_id] = [fk].[object_id] INNER JOIN [Basic] [f] ON [fk].[parent_object_id] = [f].[parent_object_id] AND [fk].[referenced_object_id] = [f].[referenced_object_id] AND [fkc].[parent_column_id] = [f].[parent_column_id] AND [fkc].[referenced_column_id] = [f].[referenced_column_id] INNER JOIN [sys].[tables] [t1] ON [f].[parent_object_id] = [t1].[object_id] INNER JOIN [sys].[columns] [c1] ON [f].[parent_object_id] = [c1].[object_id] AND [f].[parent_column_id] = [c1].[column_id] INNER JOIN [sys].[schemas] [s1] ON [t1].[schema_id] = [s1].[schema_id] INNER JOIN [sys].[tables] [t2] ON [f].[referenced_object_id] = [t2].[object_id] INNER JOIN [sys].[columns] [c2] ON [f].[referenced_object_id] = [c2].[object_id] AND [f].[referenced_column_id] = [c2].[column_id] ) SELECT [Dupl].[FKName], [Dupl].[ParentSchema], [Dupl].[ParentTable], [Dupl].[ParentColumn], [Dupl].[ReferencedTable], [Dupl].[ReferencedColumn], 'ALTER TABLE ' + [Dupl].[ParentSchema] + '.' + [Dupl].[ParentTable] + CHAR(13) + ' DROP CONSTRAINT ' + [Dupl].[FKName] [DropQuery] FROM [Dupl] GO 1234567891011121314151617181920212223242526272829303132 ;WITH [Basic]AS ( SELECT        [fk].[parent_object_id], [fkc].[parent_column_id], [fk].[referenced_object_id], [fkc].[referenced_column_id],        COUNT(*) [Cnt]   FROM   [sys].[foreign_keys] [fk]        INNER JOIN [sys].[foreign_key_columns] [fkc] ON [fkc].[constraint_object_id] = [fk].[object_id]   GROUP BY [fk].[parent_object_id], [fkc].[parent_column_id], [fk].[referenced_object_id],        [fkc].[referenced_column_id]   HAVING   COUNT(*) > 1 ),Dupl AS ( SELECT      [fk].[name] [FKName], [s1].[name] [ParentSchema], [t1].[name] [ParentTable], [ParentColumn] = [c1].[name],      [ReferencedTable] = [t2].[name], [ReferencedColumn] = [c2].[name]   FROM [sys].[foreign_keys] [fk]      INNER JOIN [sys].[foreign_key_columns] [fkc] ON [fkc].[constraint_object_id] = [fk].[object_id]      INNER JOIN [Basic] [f] ON [fk].[parent_object_id] = [f].[parent_object_id]                  AND [fk].[referenced_object_id] = [f].[referenced_object_id]                  AND [fkc].[parent_column_id] = [f].[parent_column_id]                  AND [fkc].[referenced_column_id] = [f].[referenced_column_id]      INNER JOIN [sys].[tables] [t1] ON [f].[parent_object_id] = [t1].[object_id]      INNER JOIN [sys].[columns] [c1] ON [f].[parent_object_id] = [c1].[object_id]                       AND [f].[parent_column_id] = [c1].[column_id]      INNER JOIN [sys].[schemas] [s1] ON [t1].[schema_id] = [s1].[schema_id]      INNER JOIN [sys].[tables] [t2] ON [f].[referenced_object_id] = [t2].[object_id]      INNER JOIN [sys].[columns] [c2] ON [f].[referenced_object_id] = [c2].[object_id]                       AND [f].[referenced_column_id] = [c2].[column_id] ) SELECT   [Dupl].[FKName], [Dupl].[ParentSchema], [Dupl].[ParentTable], [Dupl].[ParentColumn], [Dupl].[ReferencedTable],   [Dupl].[ReferencedColumn],   'ALTER TABLE ' + [Dupl].[ParentSchema] + '.' + [Dupl].[ParentTable] + CHAR(13) + ' DROP CONSTRAINT '   + [Dupl].[FKName] [DropQuery]FROM [Dupl]GO  
Uncategorized

Drop all foreign keys in database

Transact-SQL DECLARE @Stmt NVARCHAR(MAX) = '' SELECT @Stmt = @Stmt + 'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.NAME + '] DROP CONSTRAINT [' + fk.name + '];' FROM sys.[foreign_keys] fk INNER JOIN sys.[objects] o ON fk.[parent_object_id] = o.[object_id] EXECUTE(@Stmt) GO 12345 DECLARE @Stmt NVARCHAR(MAX) = '' SELECT @Stmt = @Stmt + 'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.NAME + '] DROP CONSTRAINT [' + fk.name + '];' FROM sys.[foreign_keys] fk INNER JOIN sys.[objects] o ON fk.[parent_object_id] = o.[object_id] EXECUTE(@Stmt) GO Transact-SQL DECLARE @Stmt NVARCHAR(MAX) SET @Stmt = '' SELECT @Stmt += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' ' + 'DROP CONSTRAINT ' + QUOTENAME(name) + ';' FROM sys.foreign_keys; EXECUTE (@Stmt) GO 1234567891011 DECLARE @Stmt NVARCHAR(MAX) SET @Stmt = '' SELECT @Stmt += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' ' + 'DROP CONSTRAINT ' + QUOTENAME(name) + ';'FROM sys.foreign_keys; EXECUTE (@Stmt)GO
Uncategorized

List Untrusted Foreign Keys for all Databases

Transact-SQL IF OBJECT_ID('tempdb..#UntrustedFK') IS NOT NULL DROP TABLE #UntrustedFK; GO CREATE TABLE [#UntrustedFK] ( [db_name] SYSNAME, [sch_name] SYSNAME, [ent_name] SYSNAME, [fk_name] SYSNAME ); EXECUTE [master].[sys].[sp_MSforeachdb] ' USE [?]; INSERT INTO #UntrustedFK SELECT ''?'' ,SCHEMA_NAME(fk.schema_id) AS [schema] ,[ob].[name] ,[fk].[name] FROM [sys].[foreign_keys] AS fk JOIN [sys].[objects] AS ob ON [fk].[parent_object_id] = [ob].[object_id] WHERE [fk].[is_not_trusted] = 1 and [fk].[is_disabled] = 0 and [fk].[is_not_for_replication] = 0; ' SELECT * FROM [#UntrustedFK] ORDER BY db_name, sch_name, ent_name, fk_name GO 12345678910111213141516171819202122232425 IF OBJECT_ID('tempdb..#UntrustedFK') IS NOT NULL DROP TABLE #UntrustedFK;GO CREATE TABLE [#UntrustedFK] ( [db_name] SYSNAME, [sch_name] SYSNAME, [ent_name] SYSNAME, [fk_name] SYSNAME); EXECUTE [master].[sys].[sp_MSforeachdb] ' USE [?];  INSERT INTO #UntrustedFK SELECT ''?''   ,SCHEMA_NAME(fk.schema_id) AS [schema]   ,[ob].[name]   ,[fk].[name]   FROM [sys].[foreign_keys] AS fk   JOIN [sys].[objects] AS ob ON [fk].[parent_object_id] = [ob].[object_id]   WHERE [fk].[is_not_trusted] = 1 and [fk].[is_disabled] = 0 and [fk].[is_not_for_replication] = 0;' SELECT * FROM [#UntrustedFK] ORDER BY db_name, sch_name, ent_name, fk_nameGO