You can use this script to truncate tables:
- When you want to clean all tables in a database/schema but keep the design and not recreate the objects
- During the initial testing period or early stages of a database design
Be very careful if using any form of this script against a production database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE databasename; DECLARE @Table NVARCHAR(50); DECLARE @Schema NVARCHAR(10); DECLARE @SQLCommand NVARCHAR(250); DECLARE Table_Cursor CURSOR FOR SELECT TABLE_SCHEMA ,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES -- Select only the tables WHERE TABLE_TYPE = 'BASE TABLE' -- Use it if you need to filter on certain schema AND TABLE_SCHEMA IN ('dbo') -- Use it if you need to skip some of the tables AND TABLE_NAME NOT IN ('RandomTable'); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @Schema, @Table WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @SQLCommand = 'TRUNCATE TABLE [' + @Schema + '].[' + @Table + '];'; EXEC (@SQLCommand); FETCH NEXT FROM Table_Cursor INTO @Schema, @Table; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor; |