IDC资讯中心
如何同时对多个表或列操作
通过使用这个存储过程，你就可以方便的对数据库中具有一定规则的或者全部表，对这里的字段进行各种操作，具体看示例！
create procedure sp_execsqlondb
(@tablename varchar(50), –表名条件
@columnname varchar(50), –字段条件
@sql nvarchar(4000), –执行的sql
@include_nti char(1) = n) –是否包含text,ntext,image数据类型
as
begin
–variable declaration
–变量定义
declare @strsql nvarchar(4000)
declare @sql2 nvarchar(4000)
declare @stablename varchar(200)
declare @scolumnname varchar(200)
declare @sqltemp nvarchar(4000)
–check whether to include text, ntext, image data types
–检查是否需要包含 text,ntext,image数据类型
set @include_nti = upper(ltrim(rtrim(@include_nti)))
if @include_nti not in (n, y)
set @include_nti = n
–construct a cursor to get the list of table/column names according to the @tablename and @columnname parameters.
–创建一个游标来读取表名和列名的列表，这里列表由参数@tablename 和 @columnname 决定
set @strsql = ndeclare tabcolcursor cursor for select rtrim(ltrim(su.name)) + . + ltrim(rtrim(so.name)), sc.name from sysobjects so inner join syscolumns sc on so.id = sc.id inner join sysusers su on so.uid = su.uid where so.xtype = u
–filter out text/ntext/image data types if it is not included
–假如不包含text/ntext/image数据类型，把他们过滤掉
if @include_nti = n
–in syscolumns sytem table xtype column corresponds to column data type
set @strsql = @strsql + and sc.xtype not in (35, 99, 34)
–add the table(s) name i.e. filter if it is supplied
–假如有提供表名参数，把它写入过滤条件中
if @tablename is not null and ltrim(rtrim(@tablename)) <>
begin
set @tablename = replace(@tablename, , , ,)
set @strsql = @strsql + and (so.name like + replace(@tablename, ,, or so.name like ) + )
set @sqltemp= and (so.name like + replace(@tablename, ,, or so.name like ) + )
end
–add the column(s) name i.e. filter if it is supplied
–假如有提供列名参数，把它写入过滤条件中
if @columnname is not null and ltrim(rtrim(@columnname)) <>
begin
set @columnname = replace(@columnname, , , ,)
set @strsql = @strsql + and (sc.name like + replace(@columnname, ,, or sc.name like ) + )
end
–execute the constructed “cursor declaration” string
–执行定义游标的sql语句
execute sp_executesql @strsql
if @@error > 0
begin
print error while declaring the cursor. please check out the parameters supplied to the procedure
return -1
end
–database transaction.
–标记一个显式本地事务的起始点
begin transaction gdatabasetrans
–open the cursor
–打开游标
open tabcolcursor
–fetch te table, column names to variables
–用游标取出标名、列名对应到参数
fetch next from tabcolcursor
into @stablename, @scolumnname
–execute the sql statement supplied in @sql parameter on every row of cursors data
–对于每一行游标取出的数据，执行由@sql参数传进来的sql语句
while @@fetch_status = 0
begin
–construct sql2 to execute supplied @sql
–by replacing @tablename, @columnname with running table name, column name of cursors data
–用游标取出的表名列名来替换@sql中的@tablename, @columnname来构造sql2
set @sql2 = @sql
set @sql2 = replace(@sql2, @tablename, @stablename)
set @sql2 = replace(@sql2, @columnname, @scolumnname)
–execute the constructed sql2
–执行sql2
execute sp_executesql @sql2
–check for errors
–检查错误
if @@error <> 0
begin
–on error, destroy objects, rollback transaction
–return -1 as unsuccessful flag
–如果发生错误，删除游标，回滚
–返回错误标记 -1
print error occurred
deallocate tabcolcursor
rollback transaction gdatabasetrans
return -1
end
–process next row of cursor
–进行下一行数据
fetch next from tabcolcursor
into @stablename,@scolumnname
end
–destroy cursor object
–删除游标
deallocate tabcolcursor
–procedure executed properly. commit the transaction.
–return 0 as successful flag
–成功完成存储过程，成功结束事务
–返回成功标记 0
commit transaction gdatabasetrans
return 0
end
使用例子
1、这个例子在northwind数据库上执行
把所有表中列名包含name的列中，把以“ltd.”结尾的列替换成“limited”。
用 select * from suppliers检查运行结果！
exec sp_execsqlondb
, –没有表名条件，针对所有表
%name%, –列名条件，列名包含“name”字符串
update @tablename set @columnname = replace(@columnname,ltd.,limited)
where @columnname like %ltd., –update 语句
n –不包含ntext,text,image数据类型
2、这个例子也在northwind数据库上执行
统计所有表中列名包含name的列的值是“quick-stop”的数量
create table ##tmp1 (table_name varchar(200),column_name varchar(200),rou_count int)
exec sp_execsqlondb
,
%name%,
declare @icount as int
select @icount=count(1) from @tablename where @columnname = quick-stop
if @icount >0
insert into ##tmp1 select @tablename,@columnname,@icount,
n
select * from ##tmp1
3、这个例子自己理解
针对所有以“employee”开头的表，以“dept”开头的字段执行存储过程。
exec sp_execsqlondb
employee%,
dept%,
exec usp_deptstates @tablename,@columnname,
n
4、还是自己理解
对@tablename @columnname参数给于多个值！
exec sp_execsqlondb
employee%,pf%,
salary,%amount%,
exec usp_employee_pf ,
n
