truncate all tables sql server 2008

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

0



How to truncate all tables sql server?

Following script truncates all the tables in SQL Server:

[sql]

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
[/sql]

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




Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

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



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.




Your Ad Here


Tags: , , , , , ,