Posted on Leave a comment

Drop all constraints (PKs & FKs) and Indexes

DECLARE @objs TABLE ( id int identity(1,1), cmd nvarchar(4000), srt tinyint )
DECLARE @id int
, @cmd nvarchar(4000)

INSERT INTO @objs (cmd,srt)
SELECT 'DROP INDEX [' + A.name + '] ON [' + + SCHEMA_NAME(B.schema_id) + + '].[' + B.name + ']'
, 1
FROM sys.indexes A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
WHERE A.is_primary_key = 0
AND A.is_unique_constraint = 0
AND B.type = 'U'
AND A.name IS NOT NULL
UNION
SELECT 'ALTER TABLE [' + + SCHEMA_NAME(B.schema_id) + + '].[' + B.name + '] DROP CONSTRAINT [' + A.name + ']'
, 2
FROM sys.objects A
INNER JOIN sys.objects B
ON A.parent_object_id = B.object_id
WHERE A.type = 'PK'
OR A.name IN ( 'PK_deemed_raiv','PK_driverchecklog')
ORDER BY 2

SELECT @id = max(id) FROM @objs

WHILE @id > 0
BEGIN
SELECT @cmd = cmd FROM @objs WHERE id = @id
EXEC sp_executesql @cmd
SET @id = @id - 1
END
GO

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.