calling stored procedure in a cursor

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 d开发者_StackOverflowifferent 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

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:

SET @sql = 'USE '+@dbName +' EXEC procs.dbo.proc_depend ' + @tableName
EXEC (@sql)

Refactor your stored proc to check for dependant objects cross database. You'll want to send it a command like this:

  exec proc_depend 'MyDatabase.dbo.MyTable';

Try this instead:

 SET @sql = ' EXEC proc_depend ''' @dbName + '.dbo.'+ @tableName + ''';

You'll need to dig into & modify proc_depend to ensure that it can take a fully qualified object name like database.schema.table