如何同时对多个表或列操作
通过使用这个存储过程，你就可以方便的对数据库中具有一定规则的或者全部表，对这里的字段进行各种操作，具体看示例！
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 = n'declare 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 cursor's 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 cursor's 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'
菜鸟学堂：
