Coding Obsession

Last week I wrote a Transact-SQL script for SQL Server 2005 that exports all database objects (stored procedures, triggers, views and so on) to individual files, grouped by object type, and I decided I’d blog about it because it was trickier than I thought at first. These are the things I had to deal with to make the script work:

• Writing text to files in SQL Server
• Getting the definition of a SQL Server database object
• Getting around the security restrictions of SQL Server

I’ll review each of them in order (if you just want to download the script skip to the end of the post).

Writing text to files in SQL Server

I didn’t have to think too much about this since someone already wrote a script to do it. The script basically inserts the text on a global temporary table and uses bulk copy to dump it into a file. However, I did have to modify it to make it work:

• The script was calling the bcp command with @@servername as the server name parameter. This was causing it to fail on my work machine (but not on my personal laptop), so I changed it to use ‘’localhost’ instead.
• I modified it to use consistent case for all identifiers so it would work on case-sensitive installations of SQL Server.

I also renamed a number of variables and formatted it a little to make it easier to read (at least in my opinion).

Getting the definition of a database object

There are a number of ways to obtain the definition (that is, the Transact-SQL source text) of a database object in SQL Server. Here are the ones I know of:

1. Call the sp_helptext stored procedure
2. Use the sys.syscomments system table (deprecated)
3. Call the OBJECT_DEFINITION function
4. Use the sys.sql_modules catalog view

It turns out you can only trust the 4th option, as the others can return outdated results. I always use sys.sql_modules anyway, because it’s the way SQL Server Management Studio does it (something I discovered by spying on it).

Getting around the security restrictions

SQL Server 2005 disables the ‘xp_cmdshell’ extended stored procedure by default (SQL Server 2000 doesn’t), so the initial script was failing with many messages like the following:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ’sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

The good thing is that the message tells you everything you need to know to solve the issue. I modified the script to enable the use of xp_cmdshell using sp_configure, and to restore the setting back to what it was after it’s finished (here I’m assuming that if you are able to run the script at all then you have the permissions to change the setting).

Special considerations for case-sensitive installations of SQL Server

I wrote the initial version of this script in a SQL Server installation that was case sensitive, and something strange happened when I tested it. The output would tell you X stored procedures were found, but there were only X-1 files in the output folder. It turned out that 2 of those stored procedures had a name that differed on the case only. Since the file system in Windows is not case-sensitive the file for one of them ended up replacing the other. I thought of modifying the script to handle this case too, but I decided it was not worth the effort.

Final notes and the script

The final script is divided into 4 stored procedures, to make things easier to understand. They are dropped at the end of execution, but end up as files in the SQL_STORED_PROCEDURE folder (I figured it was easier to just remove them from there than to make the script exclude them). You should make sure the script doesn’t overwrite any stored procedure you may already have in your database before running it.

You can grab the final script here. I hope you find it useful, but please note that I make no guarantees, so use it at your own risk! As always, I welcome all suggestions and comments about it. Happy coding!

Julián Hidalgo SQL Server SQL Server