I really like using the sql tools in visual studio that let you compare two databases schemas or data. I will often bring a copy of data from a production server and run code locally so I can test my work against real data. Often this is something I need to do over and over again. So I have found that at time it is nice to just delete all the tables in the database and use an SSIS package to just copy the data all over again. If you have more than a few tables being able to run a quick script that will delete the tables is very nice. Warning: this will delete all the tables in your database, so I suggest you be cautious about how and where you run this code. Anyhow, I use this from time to time and having it on my blog is a great place to find the code I am looking to use. I hope someone else finds this useful.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
use [DB_NAME] EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'DELETE FROM ?' DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN Exec SP_EXECUTESQL @Sql FETCH NEXT FROM @Cursor INTO @Sql END CLOSE @Cursor DEALLOCATE @Cursor GO EXEC sp_MSForEachTable 'DROP TABLE ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' |