【译文】代码生成: 利用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
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

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

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

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

利用SQL产生和执行BCP 命令

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

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

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

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

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

结论和资源

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

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