Code Generation: Using SQL to Generate SQL and Other Code

As you gain more experience as a DBA or developer, you undoubtedly will gather a bunch of tips and tricks that could increase your productivity and make your day fun. One such trick I've learned as a DBA is code generation using SQL.

You may think that SQL is only supposed to retrieve and modify data. However, if you have good understanding of SQL Server database schema and various database system tables, coupling that with some nifty string manipulation techniques, you can use SQL to generate other SQL statements, stored procedures, program commands such as BCP, and other useful code.

In this article, I will show you a few simple examples to get you started. I will provide some links to some advanced code generation techniques, such as generating stored procedures, in the resource section.

Use SQL to generate SQL

As a DBA, occasionally, people may ask you to check if data in two or more databases are the same. To have a general idea, you may want to get a list of all tables and their row counts. You probably can start typing some SQL code like "SELECT COUNT(*) FROM ORDERS", "SELECT COUNT(*) FROM CUSTOMERS", etc., until you are done with all the tables you have. This will work but it is time-consuming and error-prone, especially if you have a lot of tables in a database. If you have a good understanding of system tables, you can use SQL to generate those statements for you, instead of typing them with the keyboard. See the statement below:

Use Northwind
SELECT 'SELECT ' + CHAR(39) + NAME + CHAR(39) + ' AS TABLENAME, ' +
'COUNT(*) FROM [' + NAME + ']'
FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME NOT IN ('DTPROPERTIES')
ORDER BY NAME

All SQL server database objects are stored in the sysobjects table. The real meta data may be somewhere else, such as syscolumns, syscomments, etc. However, sysobjects can provide you with a list of all user table names, if you specify the right object type. In this case, that is all you needed. CHAR(39) will give you the single quote, so you will know the row count is for which table. Run that statement in Query Analyzer, remember to use Results in text, and you will have nice SQL statements for you to get row counts of all tables in your database.

The usage of these kind of SQL code generation is limited only by your imagination. For example, modify the above statement slightly, you can use it to generate grant statement to all the stored procedures in your database to a particular user named TESTUSER. See below.

Use SQL to generate and execute BCP commands

BCP is a very nice tool to get data in and out of your databases. It's light-weight, fast, and efficient. It can be handy if you need to import and export data to and from different databases on different servers.

Below is an example to create a data dump of all tables within Northwind. You must have a folder called C:\DataDump in order to run it properly. Or you can change it to a drive and folder of your choice. When you execute this batch of SQL, all data will be dumped into tab delimited text files within the folder specified earlier. The file names will be consistent with table names. Please refer to Books on Line for BCP references.

Modify the above script slightly, you will get BCP commands for data import. Again, in Query Analyzer, make sure Result in text is on. You can then copy the commands generated and save that to a DOS batch file. You can then do all the modification needed to make BCP import work. Notice that @TableNames is not really needed for this particular task, so the script below can be simplified. Also, if you want to test this with Northwind, I suggest you create a new database called Southwind or something like that, with the same structure as Northwind but without the data. I'll leave that as your homework;-)

Conclusions and Resources

Hopefully this article scratched the surfaces for you regarding code generation using SQL. Use this as a template to get you started. Please let me know what you think and share your favorite tips using this article's forum. Below is a list of resources that you may want to check out:

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.