VSTSC's Community Server

Community Server: The platform that enables you to build rich, interactive communities.
VSTeamSystemCentral: Your Team System community!


Welcome to VSTSC's Community Server | | Help
in Search

Applied Database Development

Dynamic T-SQL Today

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

Published Wednesday, August 05, 2009 1:09 AM by andy
Filed under: Dynamic SQL

Comments

 

Andy's Non-Technical Blog said:

Today was close. After being inspired by Tim Benninghoff 's post about Dynamic SQL (and writing one of

August 5, 2009 7:00 PM
Anonymous comments are disabled

This Blog

Syndication

  • RSS 2.0
  • Atom 1.0

Tags

Archives

Powered by Community Server (Personal Edition), by Telligent Systems