I was just reading Tim Benninghoff's latest post: Dynamic SQL is my administration buddy and it got me thinking (Tim's posts often get me thinking) about how much I use dynamic SQL each day.
Just today I needed to create a test query to count the rows in every table in a database, then create another query to truncate each table in the same database. To prove the truncation had occurred, I would need to execute the test script before and after the truncate script. There were several tables in the database.
So I built the following T-SQL script to build the counts query:
select 'select' + char(13) + char(10)
union all
select
'(select count(*) from ' + schema_name(schema_id) +
'.' + name + ') as ' + name + '_RecCount,'
from sys.tables
This output of this query resembled the following:
select
(select count(*) from dbo.incr) as incr_RecCount,
(select count(*) from dbo.PKTest) as PKTest_RecCount
(Except there were lots more tables) This was cool - it created a single row containing counts of all the tables in the database.
The truncate script read:
select
'truncate table ' + schema_name(schema_id) + '.' + name
from sys.tables
And the generated output reads:
truncate table dbo.incr
truncate table dbo.PKTest
Pretty cool. It took way less time to write the T-SQL generator code than it would have taken to type this out.
I ran the counts script, saved the results in Excel, executed the truncates, re-ran the counts script, and pasted those results into Excel below the previous results. A quick visual confirmed the truncates worked, and I have a handy artifact to attach (along with the generated scripts) to the ticket.
:{> Andy