SET NOCOUNT ON DECLARE @FKS TABLE ([Name] NVARCHAR(300) NOT NULL PRIMARY KEY, [Stmt] NVARCHAR(MAX) NOT NULL) DECLARE @Name sysname DECLARE @Stmt NVARCHAR(MAX) DECLARE @Msg NVARCHAR(MAX) INSERT INTO @FKS ( [Name], [Stmt] ) SELECT SCHEMA_NAME([schema_id]) + ‘.’ + [name], ‘ALTER TABLE [‘+ OBJECT_SCHEMA_NAME([parent_object_id]) + ‘].[‘ + OBJECT_NAME([parent_object_id]) + ‘] WITH CHECK…
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]…
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…
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…