SQL Server 2000 中动态更改的数据库

发布于 2024-08-31 09:57:55 字数 532 浏览 6 评论 0原文

在工作中，我们有许多数据库需要对其执行相同的操作。我想编写 1 SP 来循环操作并在循环开始时设置数据库（示例如下）。我尝试过 sp_executesql('USE ' + @db_id) 但这只为该存储过程的范围设置数据库。我真的不想循环使用硬编码的数据库名称，因为我们需要在许多不同的地方做类似的事情，并且如果我们添加另一个数据库，很难记住哪里需要更改。

有什么想法吗>

示例：
声明 zdb_loop 游标 FAST_FORWARD FOR 从 DBS order 中选择不同的 db_id

按 db_id OPEN zdb_loop 从 zdb_loop 获取下一个到 @db_id，

同时@@FETCH_STATUS = 0 开始使用@db_id

--Do stuff against 3 or 4 different DBs

FETCH NEXT FROM zdb_loop INTO @db_id

结束

关闭zdb_loop 解除分配 zdb_loop

原文

At work we have a number of databases that we need to do the same operations on. I would like to write 1 SP that would loop over operations and set the database at the beginning of the loop (example to follow). I've tried sp_executesql('USE ' + @db_id) but that only sets the DB for the scope of that stored procedure. I don't really want to loop with hard coded database names because we need to do similar things in many different places and it's tough to remember where things need to change if we add another DB.

Any thoughts>

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

END

CLOSE zdb_loop
DEALLOCATE zdb_loop

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

榆西 2024-09-07 09:57:55

您可以使用存储过程 sp_MSforeachdb 为此：

此示例将执行数据库
备份，然后是“DBCC CHECKDB”
每个数据库：

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:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
exec sp_MSforeachdb @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3

You can use the stored procedure sp_MSforeachdb for this:

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:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
exec sp_MSforeachdb @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3

回复收藏 0 原文