forum.venkateswarlu.net
      Register      Login
sql

4 replies to this topic

Saravanan Ramamoorhty #1
Member
58 Points
 Posted on 14 Feb 2012 01:45 PM IST how to truncate all tables in a database in sqlserver using store procedure 
SSAS     1022 views     Reply to this topic
Dhirendra Prajapati #2
Member
34 Points
 Replied on 21 Mar 2012 06:39 AM IST you can use this statement for truncate all data from all table

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

you can use this statement for delete all data from all table
USE MyDatabase
EXEC sp_MSforeachtable 'DELETE FROM ?'
 
Reply to this topic
Saravanan Ramamoorhty #3
Member
58 Points
 Replied on 22 Mar 2012 05:02 AM IST thanx dude.....
 
Reply to this topic
Laxman B #4
Member
26 Points
 Replied on 29 Mar 2012 11:29 AM IST
drop table #temp
declare @count int,
@tableName nvarchar(100)

create table #temp(
id int identity(1,1),
tablename nvarchar(100))

insert into #temp
select [name] from sys.objects where type='u'

Select @count = count(*) from #temp OPTION (KEEPFIXED PLAN)
WHILE @count > 0
BEGIN

select @tableName=tablename from #temp where id=@count
Exec('Truncate table '+@tableName)
set @count=@count-1
end

 
Reply to this topic
Laxman B #5
Member
26 Points
 Replied on 29 Mar 2012 11:30 AM IST its works fine ...


Declare @tableName varchar(200)
set @tableName=''


While exists
(
--Find all child tables and those which have no relations

select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')
and Table_type='BASE TABLE' and T.table_name > @TableName
)
Begin
Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')
and Table_type='BASE TABLE' and T.table_name > @TableName

--Truncate the table
Exec('Truncate table '+@tableName)

End

 
Reply to this topic