DROP Constraints --- Make sure we are on the correct db USE[TestDB1] GO --- Do not show record counts SET NOCOUNT ON --- Declare variables to be use in script DECLARE@ObjectIDINT , @ObjectNameNVARCHAR(500) , @SQLNVARCHAR(2000) , @CountINT , @ObjectTypenVARCHAR(1000) --- Create temp table that is going to be used throughout the script CREATETABLE #ConstraintTemp (ObjectID INTIDENTITY(1,1) NOTNULL, ObjectName NVARCHAR(250), ObjectType NVARCHAR(100)) SET@Count=0 --- First let's drop all the constraints on the tables ------ CAUTION: Running this part removes all constraints from the database ------- INSERTINTO #ConstraintTemp (ObjectName, ObjectType) SELECT Table_Schema +'.'+ Table_Name, Constraint_Name FROM INFORMATION_SCHEMA.Table_CONSTRAINTS ORDERBY constraint_type , Table_Name SELECT@ObjectID=MIN(ObjectID) FROM #ConstraintTemp WHILE@ObjectIDISNOTNULL BEGIN SELECT@ObjectName= ObjectName , @ObjectType= ObjectType FROM #ConstraintTemp WHERE ObjectID =@ObjectID SET@SQL='ALTER TABLE '+@ObjectName+' DROP CONSTRAINT ['+@ObjectType+']' EXECUTE SP_EXECUTESQL @SQL SELECT@ObjectID=MIN(ObjectID) FROM #ConstraintTemp WHERE ObjectID >@ObjectID SET@ObjectName=NULL SET@SQL=NULL SET@COUNT=@Count+1 --DROP table #ConstraintTemp END PRINTCAST(@CountASNVARCHAR(10)) +' Constraint(s) deleted'
Code use TestDB1 GO DECLARE@SqlCmdNVARCHAR(4000) declare@Trig sysname declare@owner sysname declare@uidint DECLARE TGCursor CURSORFOR SELECT name, uid FROM sysobjects WHERE type ='TR' OPEN TGCursor FETCHnextFROM TGCursor INTO@Trig, @uid WHILE@@FETCH_STATUS=0 BEGIN set@SQLCmd='drop trigger ['+user_name(@uid) +'].['+@Trig+']' exec sp_executesql @SQLCmd print@SQLCmd FETCHnextFROM TGCursor INTO@Trig, @uid END CLOSE TGCursor DEALLOCATE TGCursor GO
Code use TestDB2 declare@tablenamenvarchar(500) declare@tidint set@tid=1 selectidentity(int,1,1) as tid,name into #temp1 from sys.objects where type='U' while(@tid<(selectMAX(tid) from #temp1)+1) begin select@tablename=name from #temp1 where tid=@tid declare@namenvarchar(50) ,@errorsaveint if (rtrim(object_id(@tablename)) ='') RAISERROR ('A non-zero length table name parameter is expected', 16, 1) BEGINTRAN ifexists (select name from sysindexes where id =object_id(@tablename) and indid >0and indid <255and (status &64)=0) begin declare ind_cursor cursorfor select name from sysindexes where id =object_id(@tablename) and indid >0and indid <255and (status &64)=0 open ind_cursor fetchnextfrom ind_cursor into@name while (@@fetch_status=0) begin print'delete index '+@name exec ('drop index '+@tablename+'.'+@name) set@errorsave=@@error fetchnextfrom ind_cursor into@name end close ind_cursor deallocate ind_cursor end if (@errorsave=0) COMMITTRAN else ROLLBACKTRAN set@tid=@tid+1 end droptable #temp1
DROP Statistics use TestDB1 DECLARE@ObjectName sysname DECLARE@StatsName sysname DECLARE StatsCursor CURSOR FAST_FORWARD FOR SELECTOBJECT_NAME(object_id) as'ObjectName', [name]as'StatsName'FROM sys.stats WHERE (INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') =1ORINDEXPROPERTY(object_id, [name], 'IsStatistics') =1) ANDOBJECTPROPERTY(object_id, 'IsMSShipped') =0 OPEN StatsCursor FETCHNEXTFROM StatsCursor INTO@ObjectName, @StatsName WHILE@@FETCH_STATUS=0 BEGIN print@ObjectName+'.'+@StatsName --EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName) FETCHNEXTFROM StatsCursor INTO@ObjectName, @StatsName END CLOSE StatsCursor DEALLOCATE StatsCursor