Uncategorized

List duplicated foreign key relations

14. 1. 2019

;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]…

Read more
Uncategorized

Drop all foreign keys in database

9. 3. 2018
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 DECLARE @Stmt NVARCHAR(MAX) SET @Stmt = ” SELECT @Stmt…
Uncategorized

List Untrusted Foreign Keys for all Databases

9. 3. 2018
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…