Truncate all tables in a SQL database
Comments(2) This is a handy script that I use regularly. You may wonder why I need to truncate all my tables so frequently but that’s a different story.
When I was searching for this script, almost all results had the ‘exec’ command in the loop to truncate the tables as it looped through each. But I modified it slightly and have replaced the ‘exec’ with the ‘print’ command. What I would like the script to do is generate truncate statements for each table which I can review and in case I want to remove a few tables from it, I can comment them out and execute the rest of the block.
So here we go:
————————————————————————
DECLARE @tableName VARCHAR(1024)
DECLARE table_cur CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
OPEN table_cur
FETCH NEXT FROM table_cur INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT (‘TRUNCATE TABLE ‘+ @tableName)
FETCH NEXT FROM table_cur INTO @tableName
END
CLOSE table_cur
DEALLOCATE table_Cur
————————————————————————
Please feel free to chime in with any suggestions\recommendations!
wrong tbl_cur, use table_cur
Thanks for the correction! My variables are rarely complete, they’re always abbreviated so I made sure a corrected all of them before posting but this one slipped thru the cracks.
I’ve corrected the script above.