【译文】代码生成: 利用SQL生成SQL 和其他的代码

作者 Haidong Ji




作为一位DBA或者程序开发者,随着越来越富于经验,无疑会收集一些使工作和生活更为有趣的技巧和窍门。在SQL编程方面,我就有一个窍门。

你可能认为SQL仅仅是查询和编辑数据用的。然而,如果你很好的理解了SQL服务器的数据库图表和各种数据库系统表,通过字符串操作技术,利用SQL就可以产生出其他的SQL语句,存储过程,程序命令,如BCP,和另外的有用的代码。

在这篇文章里,我将向你显示一些简单的实例帮助你入门。并且提供一些更好的代码生成技术的资源链接。

利用SQL生成SQL

作为 DBA,偶尔会有人问起两个或者更多的数据库中的数据是否是一样的。对于一般的想法,,你可能想获得全部的数据表和它们行数的统计。你可能会写一些SQL语句,

如: “SELECT COUNT(*) FROM ORDERS”, “SELECT COUNT(*) FROM CUSTOMERS”, 等等,一直到你处理完全部的表。这个方法可行,但是会消耗大量的时间,并且有可能有错误,尤其是在数据库当中有很多数据表的时候。但是,如果你比较了解数据库的系统表,就可以使用SQL来产生这些语句,而不是用键盘敲出来,看看下面的语句:

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

全部的SQL服务器数据库对象都存储在 sysobjects 表中。还有某些实元数据,如syscolumns, syscomments, 等等。 sysobjects 可以提供全部的用户数据表名称。 如果你只是想得到对象的类型,这就足够了。CHAR(39) 表示一个单引号,这样你可以知道这个表的行数量。在Query Analyzer中运行这个语句,记着输出结果的文本,这样你就得到了一个漂亮的能够得到一个数据库全部表的行数量的SQL语句了。

这种SQL代码的生成方式,发挥想象,是没有局限的。例如,稍稍改变上面的语句,就可以将它变成对全部存储过程授权给用户TESTUSER的语句。如下:

SELECT 'GRANT EXECUTE ON [' + NAME + '] TO TESTUSER'<br />
FROM SYSOBJECTS WHERE XTYPE = 'P'

利用SQL产生和执行BCP 命令

BCP是一个非常好的工具,用来处理数据库的输入输出。它是轻量级的,迅速的和有效率的。它可以在不同的服务器不同的数据库之间灵活的输入输出数据。

下面是备份数据库 Northwind全部表的例子。需要先有一个C:\DataDump 的文件夹。也可以改变驱动器和目录。当运行这个SQL批处理的时候,全部数据都备份的选定目录的文本文件当中。文件名称有表名构成。请参考BCP信息相关书籍。

set nocount on</p>
<p>/* Initialize variables here */<br />
declare @TableNames table (TableTempID<br />
smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))<br />
declare @TableName varchar(50)<br />
declare @BackupFileName char(50)<br />
declare @BackupFolderFile varchar(150)<br />
declare @Counter smallint<br />
declare @MaxTableCount smallint<br />
declare @BackupFolder varchar(100)<br />
declare @BCPOutCommand varchar(500)</p>
<p>/* Set BackupFolder name here */<br />
set @BackupFolder = 'c:\DataDump\'</p>
<p>/* Get the list of tables that we want to backup */<br />
insert into @TableNames (TableName) select name from<br />
Northwind.dbo.sysobjects where xtype = 'U'<br />
and name not in ('dtproperties')</p>
<p>select @MaxTableCount = max(TableTempID) from @TableNames<br />
set @Counter = 1</p>
<p>/* Loop through all each table individually, generate bcp<br />
commands and run bcp commands to export data */<br />
while @Counter &amp;amp;lt;= @MaxTableCount</p>
<p>Begin</p>
<p>/* Create backup file name */<br />
select @TableName = ltrim(rtrim(TableName)) from<br />
@TableNames where TableTempID = @Counter</p>
<p>select @BackupFileName = ltrim(rtrim(@TableName)) + '.txt'</p>
<p>/* Combine backup folder name and file name */<br />
select @BackupFolderFile = @BackupFolder + @BackupFileName</p>
<p>/* Create BCP command */<br />
select @BCPOutCommand = 'bcp ' + '&amp;amp;quot;Northwind.dbo.' + @TableName +<br />
'&amp;amp;quot; out &amp;amp;quot;' + ltrim(rtrim(@BackupFolderFile)) +<br />
'&amp;amp;quot; -c -q -S' + @@Servername + ' -T'</p>
<p>print @BCPOutCommand<br />
exec master..xp_cmdshell @BCPOutCommand<br />
set @Counter = @Counter + 1<br />
end

稍微对上面的代码进行修改,可以得到输入数据的BCP命令。再在 Query Analyzer运行,确定输出文本结果。拷贝并保存为DOS批处理文件。这样你可以完成用BCP输入的工作了。对于这个任务,注意@TableNames 不是必须的,这样下面的脚本就更简单了。另外,如果想测试,建议你建立一个新的数据库, 和Northwind结构一样就可以了。

set nocount on</p>
<p>/* Initialize variables here */<br />
declare @TableNames table (TableTempID<br />
smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))<br />
declare @TableName varchar(50)<br />
declare @BackupFileName char(50)<br />
declare @BackupFolderFile varchar(150)<br />
declare @Counter smallint<br />
declare @MaxTableCount smallint<br />
declare @BackupFolder varchar(100)<br />
declare @BCPInCommand varchar(500)</p>
<p>/* Set BackupFolder name here */<br />
set @BackupFolder = 'c:\DataDump\'</p>
<p>/* Get the list of tables that we want to backup */<br />
insert into @TableNames (TableName) select name from<br />
Northwind.dbo.sysobjects where xtype = 'U'<br />
and name not in ('dtproperties')</p>
<p>select @MaxTableCount = max(TableTempID) from @TableNames<br />
set @Counter = 1</p>
<p>/* Loop through all each table individually, generate bcp<br />
commands and run bcp commands to export data */<br />
while @Counter &amp;amp;lt;= @MaxTableCount</p>
<p>Begin</p>
<p>/* Create backup file name */<br />
select @TableName = ltrim(rtrim(TableName)) from<br />
@TableNames where TableTempID = @Counter</p>
<p>select @BackupFileName = ltrim(rtrim(@TableName)) + '.txt'</p>
<p>/* Combine backup folder name and file name */<br />
select @BackupFolderFile = @BackupFolder + @BackupFileName</p>
<p>/* Create BCP command */<br />
select @BCPInCommand = 'bcp ' + '&amp;amp;quot;Northwind.dbo.' + @TableName +<br />
'&amp;amp;quot; in &amp;amp;quot;' + ltrim(rtrim(@BackupFolderFile)) + '&amp;amp;quot; -c -q -S '<br />
+ @@Servername + ' -T -E'</p>
<p>print @BCPInCommand<br />
set @Counter = @Counter + 1<br />
end

结论和资源

对于SQL代码规范编程,希望这个题目对你有所帮助。可以把它当作你开始的一个范式。

此条目发表在博客, 服务器, 程序开发分类目录,贴了, , , , 标签。将固定链接加入收藏夹。