Below Script will generate the Drop Script of all
the Constarints within a Database.
---Generates Script to Drop Constraints
(Primary Key or Unique Key)---
--USE (DatabaseName)
GO
SET NOCOUNT ON
--Declared Variables--
DECLARE @vNumDBs Int
DECLARE @vCount Int
DECLARE @SchemaName Varchar(MAX)
DECLARE @TableName Varchar(MAX)
DECLARE @ConstraintName Varchar(MAX)
----------------------
--Store Object and Schema Name--
DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) ,
TableName varchar(max),Schemaname varchar(max))
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,
SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
from sys.objects
INNER JOIN sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
and sys.indexes.type_desc!='HEAP'
GROUP BY sys.objects.name,
SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type
HAVING (sys.objects.type='U') and
sys.objects.name<>'sysdiagrams'
order by sys.objects.name --and sys.objects.name='Test1'
---------------------------------
SET @vNumDBs = @@RowCount
SET @vCount = 1
While @vCount <= @vNumDBs
BEGIN
--Get SchemaName and Object Name for Each Object--
SELECT @SchemaName=Schemaname,@TableName=TableName
FROM @Object_Holder
WHERE TabID=@vCount
--Print '['+@SchemaName+'].['+@TableName+']'
SELECT @ConstraintName= name FROM sys.indexes
WHERE is_primary_key=1
and is_unique in (0,1)
and object_id=object_id(@TableName)
--Print @vCount
If @ConstraintName<>''
BEGIN
Print 'ALTER TABLE['+@SchemaName+'].['+@TableName+']
DROP CONSTRAINT ['+@ConstraintName+']'
--SELECT @ConstraintName
END
SET @ConstraintName=''
SET @vCount = @vCount + 1
END
SET NOCOUNT OFF