Drop all PKs, FKs, and Indexes

USE [dbname] GO ---------------------------------------- -- Drop all constraints (PKs & FKs) ---------------------------------------- SET NOCOUNT ON DECLARE @id int , @objname nvarchar(256) , @constr_name nvarchar(128) , @count int , @sql nvarchar(2000) DECLARE @objs AS TABLE ( id int identity(1,1), objname nvarchar(256), objtype varchar(128)) INSERT INTO @objs ( objname, objtype ) SELECT table_schema + '.' + table_name , constraint_name FROM information_schema.table_constraints ORDER BY table_name , constraint_type SELECT @id = max(id) FROM @objs SET @count = @id WHILE @id > 0 BEGIN SELECT @objname = objname , @constr_name = objtype FROM @objs WHERE id = @id SET @sql = 'ALTER TABLE ' + @objname + ' DROP CONSTRAINT [' + @constr_name + ']' PRINT @sql --EXECUTE sp_executesql @sql SET @id = @id - 1 SET @count = @count + 1 END PRINT CAST(@count as varchar(10)) + ' Constraint(s) deleted' GO ---------------------------------------- -- Drop all Indexes ---------------------------------------- SET NOCOUNT ON DECLARE @id int , @tabname nvarchar(128) , @idxname nvarchar(128) , @ispk tinyint , @count int , @sql nvarchar(2000) DECLARE @objs AS TABLE ( id int identity(1,1), tabname nvarchar(128), idxname varchar(128), ispk tinyint ) INSERT INTO @objs ( tabname, idxname, ispk ) SELECT OBJECT_NAME(si.object_id) "tabname" , si.name "idxname" , si.Is_Primary_Key "ispk" FROM sys.indexes si LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1 AND si.name IS NOT NULL ORDER BY 1, 2 SELECT @id = max(id) FROM @objs WHILE @id > 0 BEGIN SELECT @tabname = tabname , @idxname = idxname , @ispk = ispk FROM @objs WHERE id = @id SET @sql = CASE @ispk when 1 then 'ALTER TABLE [' + @tabname + '] DROP CONSTRAINT [' + @idxname + ']' else 'DROP INDEX [' + @idxname + '] ON [' + @tabname + '] WITH ( ONLINE = OFF )' END PRINT @sql SET @id = @id - 1 END GO