Generating multiple SQL statements from a query

2

December 17, 2012 by Kenneth Fisher

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.

About these ads

2 thoughts on “Generating multiple SQL statements from a query

  1. R Silver says:

    Would you elaborate a bit on this statement: ‘Make sure that your code is case-sensitive.’ ? I think that I know what you are saying, but I want to be clear about it. I don’t normally work with SQL Server, but I working my way towards working as a SQL Server DBA. I have been a DB2 DBA for 10 years. Thanks!

    • When you install an instance of SQL Server you specify a collation for the server. This can then be over-ridden at a database or column level. Heck you can even over-ride it in a query. Here is an example of a collation: SQL_Latin1_General_CP1_CI_AS. The CI near the end stands for Case Insensative. If it was CS that would be Case Sensative.

      When you run a query the collation setting determines if what you are doing is case sensitive or not. If your database collation is case sensitive then the system tables are using a case sensitive collation. This means that the code you run on that particular database will be treated as case sensitive or not depending on the collation.

      Keywords are always going to be case insensitive. SELECT, Select, and select all will work, however if you are working in a case sensitive environment and have a table named OrderDetail and you run a select “SELECT * FROM orderdetail” you are going to get an error saying the table isn’t found.

      Because of this let’s say your dynamic sql is testing if a table named ‘DBA’ exists in your database. You try to run “SELECT * FROM sys.Tables WHERE name = ‘DBA’ ” on each of your databases. This will work just fine on case insensitive databases, however as soon as you hit a case sensitive database you will get an error. The table name is actually sys.tables, all lower case. For that matter if you happened to hit on a column that is case sensitive even though the rest of the database, or even the table, is case insensitive you are still going to run into problems.

      Hope that helps. I’m by no means an expert on case sensitivity so I may have gotten some of the details wrong, but in general that’s the way it works.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 348 other followers

%d bloggers like this: