sp_depend with a cursor

I have a bunch of Views in my DB that I would like to get the dependencies listed for. Currently, I'm using the 'sp_depends' sproc to do this. To speed up the process, I'm attempting to use the sp_depends sproc in a cursor that iterates over the list of views. However, I'm not having any luck and I've spent an embarrassing amount of time trying to "shotgun" fixit. Below is what I've got thus far.

我的DB中有很多视图，我想要列出依赖项。目前，我正在使用“sp_depend”sproc进行此操作。为了加快进程，我尝试在一个遍历视图列表的游标中使用sp_depend sproc。然而，我没有任何运气，我花了相当多的时间试图用“霰弹枪”来解决问题。下面是我到目前为止得到的。

DECLARE @ViewNames TABLE
(
     ViewName VARCHAR(255)
)
INSERT INTO @ViewNames
select name  from [AMF_Article].sys.views

declare @tableCursor cursor,
         @viewName varchar(100);
set @tableCursor = cursor for select ViewName from @ViewNames

open @tableCursor
fetch next from @tableCursor into @viewName
while(@@fetch_status = 0)
begin

    declare @sql varchar(max)
    set @sql = 'sp_depends ''[dbo].' + @viewName + ''
    PRINT @sql
    exec @sql

 fetch next from @tableCursor into @viewName
end

I think something is going on with the quoting combined with the EXEC call. I can't get my single quotes to match up, and when I do, it still tells me no. When I run the statement

我认为引用和执行委员会的电话有关系。我不能让我的单引号匹配起来，当我这么做的时候，它仍然告诉我不。当我运行语句时

sp_depends '[dbo].[V_AMF_Distinct_Products]'

all is well, but in a loop, not so much.

一切都很好，但在循环中，不是那么好。

Any help is greatly appreciated.

非常感谢您的帮助。

3 个解决方案

#1

Aside all performance considerations, take a look at slightly changed version of your script and see if it works for you:

抛开所有性能考虑，看看您的脚本的稍微修改过的版本，看看它是否适合您:

DECLARE @tableCursor CURSOR
DECLARE @viewName sysname;
DECLARE @schemaName sysname; 
SET @tableCursor = CURSOR FOR
    SELECT SS.name SchemaName, SV.name ViewName FROM sys.views SV
    JOIN sys.schemas SS ON SS.schema_id=SV.schema_id

OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @schemaName, @viewName
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql varchar(max)
    SET @sql = 'sp_depends '''+QUOTENAME(@schemaName) + '.' + QUOTENAME(@viewName) + ''''
    PRINT @sql
    EXEC(@sql)
    FETCH NEXT FROM @tableCursor INTO @schemaName, @viewName
END
CLOSE @tableCursor
DEALLOCATE @tableCursor

Updated 16-07-2016, thanks to Sean

更新16-07-2016，感谢Sean。

To exclude cursor logic, you can also use local variable assignment like below:

为了排除游标逻辑，您还可以使用如下的局部变量赋值:

DECLARE @sql nvarchar(MAX)=''
SELECT @sql += 'sp_depends '''+QUOTENAME(SS.name) + '.' + QUOTENAME(SV.name) + ''';'
    FROM sys.views SV
    JOIN sys.schemas SS ON SS.schema_id=SV.schema_id

EXEC(@sql)

#2

Here is a way to do this using sys.views to build your dynamic sql string. No need for cursors here.

这里有一个使用sys的方法。创建动态sql字符串的视图。这里不需要游标。

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'exec sp_depends ''' + QUOTENAME(name) + ''';'
from sys.views

exec sp_executesql @SQL

#3

Cursors suck :-). Instead of executing the proc in a cursor why not just directly query what you want in a single query directly from the system tables. The following will give you the actual script used by sp_depends:

游标吸:-)。与其在游标中执行proc，不如直接从系统表直接查询您想要的查询。下面将给出sp_depend使用的实际脚本:

sp_helptext 'sp_depends'

Look at the script. Grab the select statement. Update the select and where clause to grab what you need. Don't accidentally overwrite or change the actual sp_depends proc or your DBA will kill you.

看看脚本。抓住select语句。更新select和where子句以获取所需的内容。不要意外地覆盖或更改实际的sp_depend proc，否则DBA会杀了您。

#1