A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, February 9, 2010

Yo dawg, I heard you like TSQL

So I put TSQL in your TSQL so you can query while you query."  Or so some horrible approximation of the meme goes.  

In a previous life, I had the pleasure to work with an Oracle DBA, Jerry Dickinson, who taught me a nifty trick---write a query that generates a query.  I've applied that lesson in many aspects of my career since then.  I have scripts that build scripts for SSIS config values, scripts that go in and fix tables after the fact, etc.  For example, our database tables typically have the concept of last modified user and date.  Being lazy, I'd much rather have a default fill those values instead of coding for it in every insert statement.  Rather than writing N statements of "ALTER TABLE dbo.FOO DEFAULT system_user FOR last_update_user" I'd prefer to write a script that does that for me.  This becomes especially handy when the exact field name will change based on the type of table it is.  

-- This generates the default constraint for the last modified user
SELECT
   'ALTER TABLE dbo.' + T.name + ' ADD DEFAULT system_user FOR ' + SC.name AS defaults_sql
FROM
    sys.tables T
    INNER JOIN
        sys.columns SC
        ON SC.object_id = T.object_id
WHERE
    SC.name IN ('last_upd_id', 'insrt_id')

The truly brave among you can wire that up to a cursor, cry havoc and execute the statements.  Myself, I prefer to inspect them and persist the changes to a file before running them. Repeatability is a good thing.  ctrl-T will dump results to text which I prefer to having SSMS dump directly to file ctrl-shift-F

The concept of building statements with strings should be applicable regardless of your database platform (and even outside the database) but do be aware of your language features to take full advantage of them

No comments: