Uncategorized

Drop all foreign keys in database (WHILE version)

18. 6. 2019
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO
DECLARE @SchemaName NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @ConstraintName NVARCHAR(128)
DECLARE @Stmt NVARCHAR(MAX)
DECLARE @FKsToDel TABLE ([SchemaName] NVARCHAR(128) NOT NULL,
[TableName] NVARCHAR(128),
[ConstraintName] NVARCHAR(128),
PRIMARY KEY ([SchemaName], [TableName], [ConstraintName]))
INSERT INTO @FKsToDel
( [SchemaName], [TableName], [ConstraintName] )
SELECT [s].[name], [t].[name], [c].[name]
FROM [sys].[objects] AS [c]
INNER JOIN [sys].[tables] AS [t] ON [c].[parent_object_id] = [t].[object_id]
INNER JOIN [sys].[schemas] AS [s] ON [t].[schema_id] = [s].[schema_id]
WHERE [c].[type] IN ('F') AND [s].[name] IN (/*Fill list*/)
WHILE EXISTS (SELECT * FROM @FKsToDel)
BEGIN
SELECT TOP(1)
@SchemaName = [SchemaName],
@TableName = [TableName],
@ConstraintName = [ConstraintName]
FROM @FKsToDel
ORDER BY [SchemaName], [TableName], [ConstraintName]
PRINT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
N' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName) + ';' + CHAR(13) + 'GO'
DELETE FROM @FKsToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName AND [ConstraintName] = @ConstraintName
END
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON GO DECLARE @SchemaName NVARCHAR(128) DECLARE @TableName NVARCHAR(128) DECLARE @ConstraintName NVARCHAR(128) DECLARE @Stmt NVARCHAR(MAX) DECLARE @FKsToDel TABLE ([SchemaName] NVARCHAR(128) NOT NULL, [TableName] NVARCHAR(128), [ConstraintName] NVARCHAR(128), PRIMARY KEY ([SchemaName], [TableName], [ConstraintName])) INSERT INTO @FKsToDel ( [SchemaName], [TableName], [ConstraintName] ) SELECT [s].[name], [t].[name], [c].[name] FROM [sys].[objects] AS [c] INNER JOIN [sys].[tables] AS [t] ON [c].[parent_object_id] = [t].[object_id] INNER JOIN [sys].[schemas] AS [s] ON [t].[schema_id] = [s].[schema_id] WHERE [c].[type] IN ('F') AND [s].[name] IN (/*Fill list*/) WHILE EXISTS (SELECT * FROM @FKsToDel) BEGIN SELECT TOP(1) @SchemaName = [SchemaName], @TableName = [TableName], @ConstraintName = [ConstraintName] FROM @FKsToDel ORDER BY [SchemaName], [TableName], [ConstraintName] PRINT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName) + ';' + CHAR(13) + 'GO' DELETE FROM @FKsToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName AND [ConstraintName] = @ConstraintName END GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO

DECLARE @SchemaName NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @ConstraintName NVARCHAR(128)
DECLARE @Stmt NVARCHAR(MAX)
DECLARE @FKsToDel TABLE ([SchemaName] NVARCHAR(128) NOT NULL,
[TableName] NVARCHAR(128),
[ConstraintName] NVARCHAR(128),
PRIMARY KEY ([SchemaName], [TableName], [ConstraintName]))

INSERT INTO @FKsToDel
( [SchemaName], [TableName], [ConstraintName] )
SELECT [s].[name], [t].[name], [c].[name]
FROM [sys].[objects] AS [c]
INNER JOIN [sys].[tables] AS [t] ON [c].[parent_object_id] = [t].[object_id]
INNER JOIN [sys].[schemas] AS [s] ON [t].[schema_id] = [s].[schema_id]
WHERE [c].[type] IN ('F') AND [s].[name] IN (/*Fill list*/)

WHILE EXISTS (SELECT * FROM @FKsToDel)
BEGIN

SELECT TOP(1)
@SchemaName = [SchemaName],
@TableName = [TableName],
@ConstraintName = [ConstraintName]
FROM @FKsToDel
ORDER BY [SchemaName], [TableName], [ConstraintName]

PRINT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
N' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName) + ';' + CHAR(13) + 'GO'

DELETE FROM @FKsToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName AND [ConstraintName] = @ConstraintName

END
GO

 

69
Share: