在游标中调用存储过程
我创建了一个 SQL Server 2005 存储过程，它查找特定表上的依赖对象。
我想为不同的数据库和不同的表运行这个存储过程。我为此创建了光标。
当我编写 USE @dbname 时，它会尝试在 @dbname 中查找存储过程，而不是在当前数据库中。
有人可以帮我如何在光标中写这个命令吗？
DECLARE name_cur CURSOR FOR
SELECT db_name, obj_name from Stats_Usage
WHERE last_user_update > '2011-06-01' ORDER BY db_name
DECLARE @tableName NVARCHAR (800)
DECLARE @dbName NVARCHAR(800)
DECLARE @sql NVARCHAR(900)
OPEN name_cur
FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN
SET @sql = 'USE '+@dbName +' EXEC proc_depend ' + @tableName
EXEC (@sql)
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur
GO
I have created a SQL Server 2005 stored procedure which finds dependent objects on a particular table.
I want to run this stored procedure for different database and for different tables. I have created cursor for this.
When I write USE @dbname, it tries to find the stored procedure in a @dbname and not the current database.
Can anybody please help me with how do I write this command in a cursor?
DECLARE name_cur CURSOR FOR
SELECT db_name, obj_name from Stats_Usage
WHERE last_user_update > '2011-06-01' ORDER BY db_name
DECLARE @tableName NVARCHAR (800)
DECLARE @dbName NVARCHAR(800)
DECLARE @sql NVARCHAR(900)
OPEN name_cur
FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN
SET @sql = 'USE '+@dbName +' EXEC proc_depend ' + @tableName
EXEC (@sql)
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur
GO
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以完全限定您的存储过程名称。
假设您的 SP 所在的数据库名为
procs（例如），您可以修改查询以使用以下内容：You can fully qualify your Stored Procedure name.
Assuming the database your SP resides in is called
procs(for example), you could amend your query to use the following:重构存储过程以检查跨数据库的依赖对象。您需要向它发送这样的命令：
试试这个：
您需要深入研究 &修改 proc_depend 以确保它可以采用完全限定的对象名称，例如database.schema.table
Refactor your stored proc to check for dependant objects cross database. You'll want to send it a command like this:
Try this instead:
You'll need to dig into & modify
proc_dependto ensure that it can take a fully qualified object name likedatabase.schema.table