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 NAMEAll 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.
SELECT 'GRANT EXECUTE ON [' + NAME + '] TO TESTUSER' FROM SYSOBJECTS WHERE XTYPE = 'P'
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.
set nocount on
/* Initialize variables here */declare @TableNames table (TableTempID
smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))
declare @TableName varchar(50)
declare @BackupFileName char(50)
declare @BackupFolderFile varchar(150)
declare @Counter smallint
declare @MaxTableCount smallint
declare @BackupFolder varchar(100)
declare @BCPOutCommand varchar(500)
/* Set BackupFolder name here */set @BackupFolder = 'c:\DataDump\'
/* Get the list of tables that we want to backup */insert into @TableNames (TableName) select name from
Northwind.dbo.sysobjects where xtype = 'U'
and name not in ('dtproperties')
select @MaxTableCount = max(TableTempID) from @TableNames
set @Counter = 1
/* Loop through all each table individually, generate bcp
commands and run bcp commands to export data */while @Counter <= @MaxTableCount
Begin
/* Create backup file name */select @TableName = ltrim(rtrim(TableName)) from
@TableNames where TableTempID = @Counter
select @BackupFileName = ltrim(rtrim(@TableName)) + '.txt'
/* Combine backup folder name and file name */select @BackupFolderFile = @BackupFolder + @BackupFileName
/* Create BCP command */select @BCPOutCommand = 'bcp ' + '"Northwind.dbo.' + @TableName +
'" out "' + ltrim(rtrim(@BackupFolderFile)) +
'" -c -q -S' + @@Servername + ' -T'
print @BCPOutCommand
exec master..xp_cmdshell @BCPOutCommand
set @Counter = @Counter + 1
end
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;-)
set nocount on
/* Initialize variables here */declare @TableNames table (TableTempID
smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))
declare @TableName varchar(50)
declare @BackupFileName char(50)
declare @BackupFolderFile varchar(150)
declare @Counter smallint
declare @MaxTableCount smallint
declare @BackupFolder varchar(100)
declare @BCPInCommand varchar(500)
/* Set BackupFolder name here */set @BackupFolder = 'c:\DataDump\'
/* Get the list of tables that we want to backup */insert into @TableNames (TableName) select name from
Northwind.dbo.sysobjects where xtype = 'U'
and name not in ('dtproperties')
select @MaxTableCount = max(TableTempID) from @TableNames
set @Counter = 1
/* Loop through all each table individually, generate bcp
commands and run bcp commands to export data */while @Counter <= @MaxTableCount
Begin
/* Create backup file name */select @TableName = ltrim(rtrim(TableName)) from
@TableNames where TableTempID = @Counter
select @BackupFileName = ltrim(rtrim(@TableName)) + '.txt'
/* Combine backup folder name and file name */select @BackupFolderFile = @BackupFolder + @BackupFileName
/* Create BCP command */select @BCPInCommand = 'bcp ' + '"Northwind.dbo.' + @TableName +
'" in "' + ltrim(rtrim(@BackupFolderFile)) + '" -c -q -S '
+ @@Servername + ' -T -E'
print @BCPInCommand
set @Counter = @Counter + 1
end
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: