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:
Post a Comment