How to truncate all table in mssql

First remove all relationship

Use CurrentData
exec sp_MSforeachtable ‘TRUNCATE TABLE ?’

Use CurrentData
EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT all’
exec sp_MSforeachtable ‘TRUNCATE TABLE ?’
while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE=’FOREIGN KEY’))
begin
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=(‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.[‘ + TABLE_NAME
+ ‘] DROP CONSTRAINT [‘ + CONSTRAINT_NAME + ‘]’)
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’
exec (@sql)
end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s