Dynamic SQL is one of my favorite tools in the DBA toolbox. I’m big on automation and dynamic SQL can be very helpful in automating processes across multiple databases, multiple instances etc. There are several flavors of dynamic SQL and each is useful under different circumstances. I should point out that dynamic SQL is a tool like any other tool. It isn’t always going to be the right solution. In my opinion one of the main differences between a junior DBA and a mid level DBA is the number of tools they know. And knowing when to use a given tool is one of the main differences between a mid level DBA and a senior DBA.

Generating multiple SQL statements from a query is probably one of the simplest forms of dynamic SQL. You execute one query and it generates a series of other queries as its output. You then copy and paste the output into a query window and execute those queries. One of the many occasions that I’ve used this method of dynamic SQL is generating DBCC CHECKDB statements for each database on the instance. Now you might ask, “Why not use sp_msforeachdb?” My answer is that you can, certainly. And if this is a regularly scheduled task then it would certainly be appropriate. However what if you only want to run the CHECKDB on a portion of the databases? Or if time is important? Let’s say that we are running CHECKDBs after the restores in a DR test and the process is being timed? Or for that matter a real disaster when time is essential. I’ve found over the years that if I run 2 or even 3 query windows, each one running a series of CHECKDBs I can cut my run time by 40-60% depending on the server. Again, knowledge of the server is important here, running CHECKDB on multiple large databases may cause tempdb problems and make things worse than they would have been otherwise.

Here is my syntax for generating the CHECKDBs.

 SELECT 'DBCC CHECKDB (['+name+']) WITH NO_INFOMSGS;'<br />
FROM sys.databases 

When I build a command like this I go through a few simple steps. First I build my command using a sample database, table, whatever. So in this case:

DBCC CHECKDB ([master]) WITH NO_INFOMSGS;

When I create the command I always make sure to be as exact as I possibly can. So I make sure to use semi-colons (;) at the end of the command, brackets ([]) around the name of the database etc. For those who don’t know, in SQL a semi-colon signifies the end of a command and brackets must be put around identifiers that do follow the rules for identifiers. For example using special characters, reserved words etc. I always put the brackets around an identifier when I’m building my command for dynamic SQL because you never know what type of identifier you will get. For example one of my production boxes has a database named similar to the following:

SharePoint_AdminContent_82r31932-fea0-457b-878a-3f6c40qc20df

And unsurprisingly without the brackets the CHECKDB command will give me an error. As another example how many of you have seen a table or column named ORDER or DATABASE.

Once I have my command created I put single quotes around it and replace the “variable” parts with the field name in the format of ‘+fieldname+’.

So

DBCC CHECKDB ([master]) WITH NO_INFOMSGS;

becomes

'DBCC CHECKDB (['+name+']) WITH NO_INFOMSGS;'

And last but not least wrap that with a SELECT statement.

 SELECT 'DBCC CHECKDB (['+name+']) WITH NO_INFOMSGS;'<br />
FROM sys.databases 

Once you run the command you will get output similar to the following:

DBCC CHECKDB ([master]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([tempdb]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([model]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([ReportServer$SQL2008]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([ReportServer$SQL2008TempDB]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([Tests]) WITH NO_INFOMSGS;<br />
DBCC CHECKDB ([AdventureWorks2008]) WITH NO_INFOMSGS;

From there you can take the output and paste it into a single query window, break it up into pieces and paste it into multiple query windows etc. You can work with it however you want.

Some other places I’ve used this technique include creating RESTORE commands, running sp_spaceused on multiple tables and adding one or more control columns to a group of tables (createdate and lastupdate for example).

One last warning when creating this type of dynamic SQL. Frequently you will be creating code that will run on multiple databases or even run on multiple instances. Make sure that your code is case-sensitive. There is a good chance that your code is going to hit a case-sensitive database or instance at some point.

I can’t really stress this enough. When you are working with dynamic SQL there are a lot of variables. Between developers creating SQL objects, “purchased” products and the DBA that worked here 10 years ago you never know what you are going to see. The more careful you are when creating your dynamic SQL the less often you will run into problems with something unexpected.