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.