Dynamic Truncate Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Truncate Query?

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-02-23 : 14:31:30

Hi all - new to these forums and also a bit rusty on my SQL

What I need to do is loop through all the tables in a given database and truncate ONLY the ones where the table name starts with "X5.src_".

Any help will be much appreciated!

mfemenel
Professor Frink

1421 Posts

Posted - 2010-02-23 : 14:53:16

Something like this. Just change the tablename piece a bit.

Declare @tablename nvarchar(1000)
Declare @sql nvarchar(1000)
declare @schemaname varchar(10)
set @schemaname='x5'
select st.[name] as TableName
INTO #DONE
from sys.tables st
inner join sys.schemas ss
on st.schema_id=ss.schema_id
where ss.name=@schemaname

WHILE EXISTS(Select tablename from #done)
BEGIN
select @tablename=TableName
FROM #DONE

set @sql='truncate table ' + @schemaname + '.' + @tablename
--SELECT @SQL
EXEC sp_executesql @sql
DELETE FROM #Done where tablename=@tablename
END
DROP TABLE #Done

Mike
"oh, that monkey is going to pay"

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-02-23 : 15:24:43

Thanks, Mike, that worked like a charm!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 02:14:20

or

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'X5.src_%'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-24 : 03:16:18

quote:
Originally posted by madhivanan

or

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'X5.src_%'
exec(@sql)

A small correction:

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'src_%' and Table_Schema ='X5'
exec(@sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 04:08:42

quote:
Originally posted by pk_bohra

quote:
Originally posted by madhivanan

or

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'X5.src_%'
exec(@sql)

A small correction:

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'src_%' and Table_Schema ='X5'
exec(@sql)

Thanks. That makes more sense

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-24 : 04:14:44

quote:
Originally posted by madhivanan

quote:
Originally posted by pk_bohra

quote:
Originally posted by madhivanan

or

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'X5.src_%'
exec(@sql)

A small correction:

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'src_%' and Table_Schema ='X5'
exec(@sql)

Thanks. That makes more sense

Madhivanan

Failing to plan is Planning to fail

I have learned a lot from you..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 04:16:23

quote:
Originally posted by pk_bohra

quote:
Originally posted by madhivanan

quote:
Originally posted by pk_bohra

quote:
Originally posted by madhivanan

or

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'X5.src_%'
exec(@sql)

A small correction:

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' truncate table '+table_name from information_schema.tables
where table_type='BASE TABLE' and table_name like 'src_%' and Table_Schema ='X5'
exec(@sql)

Thanks. That makes more sense

Madhivanan

Failing to plan is Planning to fail

I have learned a lot from you..

You are welcome

Madhivanan

Failing to plan is Planning to fail

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-02-24 : 09:44:21

Thanks to all of you - it's embarassing how much I've forgotten in 3 years, I used to be able to do stuff like this cold.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources