如何同時對多個表或列操作
通過使用這個存儲過程，你就可以方便的對數據庫中具有一定規則的或者全部表，對這里的字段進行各種操作，具體看示例！
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'
菜鳥學堂：
