Code that writes code

Code that writes code it one of the best productivity tips I can give you.  And no, I’m not talking about ORMs. I’m specifically talking about YOU writing a query, which writes one or more queries.

The example I’ll give today is changing database file growth rates. If I want to change the DB file growth rates to 1 GB for more than one database, it quickly becomes a pain.

One or two queries? Write your T-SQL code by hand.

ONE database isn’t so bad. I simply look up the logical file name (using EXEC sp_HelpDB dbname), and plug those names into a couple of ALTER DATABASE statements:

USE [tempdb]; 
ALTER DATABASE [tempdb] MODIFY FILE (NAME=[tempdev], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);
USE [tempdb]; 
ALTER DATABASE [tempdb] MODIFY FILE (NAME=[templog], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);

No problem. But what if I want to do two databases? Or three? Or ALL databases on a server? That’s a lot of looking up, copy-pasting, and plugging in.

No fun. No fun at all.

Enter: code that writes code!

Many, many queries: generated T-SQL code

We can simply generate all of those statements, using information from sys.master_files:

SELECT 'USE [' + db_name(database_id) + ']; 
 ALTER DATABASE [' + db_name(database_id) + '] MODIFY FILE (NAME=[' 
+ name + '], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);', *
FROM sys.master_files
WHERE 1=1
 AND [type] in (0, 1)
 AND db_name(database_id) IN ('tempdb', 'model', 'DB1')
ORDER BY database_id, [type];

This is GENUINELY WONDERFUL. We get a whole list of this:

USE [tempdb]; 
 ALTER DATABASE [tempdb] MODIFY FILE (NAME=[tempdev], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);
USE [tempdb]; 
 ALTER DATABASE [tempdb] MODIFY FILE (NAME=[templog], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);
USE [model]; 
 ALTER DATABASE [model] MODIFY FILE (NAME=[modeldev], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);
USE [model]; 
 ALTER DATABASE [model] MODIFY FILE (NAME=[modellog], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);
USE [DB1]; 
 ALTER DATABASE [DB1] MODIFY FILE (NAME=[DB12], filegrowth = 1024 MB, MAXSIZE = UNLIMITED);
USE [DB1]; 
...

I keep that query around, and a few more like it, for the times I need them. That’s even MORE convenient: I don’t even have to write the code that writes the code more than once.

And much, much more!

And you can do this with all kinds of things. I’ve used the same technique to write the Minion.CloneSettings stored procedure, that’s a part of all our free Minion Maintenance modules.

You simply run, for example:

EXEC Minion.Clonesettings 'Minion.BackupSettings', 1;

And the stored procedure will give you an INSERT statement, already filled out, for that particular table (Minion.BackupSettings):

BEGIN TRAN; 
INSERT INTO Minion.BackupSettings ([DBName], [Port], [BackupType], [Exclude], [GroupOrder], [GroupDBOrder], [Mirror], ...) 
SELECT 'MinionDefault' AS [DBName], NULL AS [Port], 'All' AS [BackupType], 0 AS [Exclude], 0 AS [GroupOrder], 0 AS [GroupDBOrder], 0 AS [Mirror], ...
-- ROLLBACK TRAN;
COMMIT TRAN;

It’s very convenient to have a ready-made insert statement, with values based on an existing row!