Question

在工作上,我们拥有一些数据库,我们需要在同样的行动中这样做。我想写一下1个方案,它将接管业务,并在整段开始时建立数据库(随后可效仿)。我曾尝试过间谍——executesql(USE + @db_id),但这只是为这种储存程序的范围设置了银。我并不真的想要掌握硬编码的数据库名称,因为我们需要在许多不同地方做类似的事情,如果我们增加另一个行,那就很难记住需要改变什么。

任何思想和思想;

Example:
DECLARE zdb_loop CURSOR FAST_FORWARD FOR SELECT distinct db_id from DBS order by db_id

OPEN zdb_loop FETCH NEXT FROM zdb_loop INTO @db_id

WHILE @@FETCH_STATUS = 0 BEGIN USE @db_id

--Do stuff against 3 or 4 different DBs

FETCH NEXT FROM zdb_loop INTO @db_id

出席情况

CLOSE zdb_loop DEALLOCATE zdb_loop

Answer 1

到目前为止,看起来像动态是做到这一点的唯一途径。 Pretty lame.

Answer 2

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm” rel=“nofollow noreferer”_sp_MSforeachdb

This example will perform a database backup, then a "DBCC CHECKDB" against each database:

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 =
    if   ?   <>   tempdb   print   *** Processing DB ? ***   
set @cmd2 =  if   ?   <>   tempdb   backup database ? to disk=  c:	emp?.bak   
set @cmd3 =  if   ?   <>   tempdb   dbcc checkdb(?) 
exec sp_MSforeachdb @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3

友情链接