How to truncate all the tables in a database
We do get scenarios where we get request to truncate all the data in the tables and provide only the structure of it. These kind of scenarios do arise at times of testing or building any project.
Truncating all the data is not so easy as there might be some relational tables which makes the job tough. We have to delete the child table data first then handle the parent table to ensure we don't get any errors while deleting/truncating the data.
Other way around is drop the foreign keys in the database by taking the backup of them and then delete or truncate the data. Recreate the foreign keys to ensure the structure of the RDBMS is intact.
Below are the steps to proceed further.
1. Take the backup of Foreign keys in the database by following the below link.
2. Now run the below code to truncate the data.
a. You can modify the code by if you want to truncate or delete specific tables or objects by applying the proper conditions in where clause.
b. I have commented the execute step in the below cursor. First check the code and see the output as i have given the print output option. Remove the commented lines and then re-execute the code to truncate the data.
Declare @c1 cursor
declare @name nvarchar(100)
declare @sql nvarchar(150)
set @C1 = CURSOR for
select distinct '['+SCHEMA_NAME(schema_id)+'].['+name+']' from sys.objects where type = 'U'
open @C1
fetch next from @C1 into @name
while (@@fetch_status=0)
Begin
set @sql= 'Truncate table '+@name
--exec (@sql)
Print @name+' truncated successfully '
fetch next from @C1 into @name
End
close @C1
Deallocate @C1
3. Now re-create all the foreign keys as stated in the 1st step.
Thanks.
