truncate all tables sql server 2008

Posted on : 27-04-2012 | By : Devi Prasad | In : T-SQL Code Examples

Share:

0


How to truncate all tables sql server?

Following script truncates all the tables in SQL Server:


DECLARE @tablename AS VARCHAR (1000)
 
DECLARE @sql AS VARCHAR (1000)
 
IF OBJECT_ID('tempdb.dbo.#tables') IS NOT NULL
    DROP TABLE #tables
 
SELECT *
INTO   #tables
FROM   sys.tables
 
WHILE EXISTS (SELECT *
              FROM   #tables)
    BEGIN
        SELECT @tablename = name
        FROM   #tables
        SELECT @sql = 'truncate table ' + @tablename;
        PRINT @sql
        EXECUTE (@sql)
        DELETE #tables
        WHERE  name = @tablename;
    END

Works if the tables do not have foriegn key constrains or schema binding relations with other tables/objects in the database.

(Visited 97 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , ,