希望观察存在于多个数据库和多个服务器上的存储过程

Question

在MS SQL 2005 Enterprise多台服务器上遇到问题，我想在多台服务器和多个数据库中获取元数据集合。我在Stack Overflow上看到了一个使用神奇的sp_MSforeachdb的好例子，我在下面做了一些修改。基本上，MS存储过程是动态运行的，它随时都在寻找数据库（？）类似于"Case（fourspaces）"这样的名称。这太棒了，它给了我想要的，但只需要一台服务器。我想做更多，这可能是SQL大师吗？

迄今为止的例子：

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName varchar(256))
INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb 'select distinct @@SERVERNAME+''.''+ ''?'' + ''.'' + p.name from [?].sys.procedures p (nolock) where ''?'' like ''Case____'''
SELECT * FROM @AllTables ORDER BY 1

有没有一种方法可以在SQL、Linq或ADO.NET中实现这一点，以执行这种巧妙的内置存储过程，该过程插入到表变量中，以便在服务器之间多次执行此操作，但是。。。。。。把它放在一套里。据我所知，在SQL Management Studio中，你不能在一个会话中切换服务器，但我希望在这一点上被证明是错误的。

我有一个有8台服务器的生产环境，每台服务器都有许多数据库。我可以运行多次，但我希望如果服务器已经链接，我可以从系统视图中以某种方式执行此操作。然而，我在一个使用SQL 2005的环境中，并获得了MS对sys视图的下载，看起来sys.servers就像是在一个孤岛上，SERVERID似乎没有加入其他任何东西。

我愿意在C#环境中使用ADO.NET阅读器或LINQ，并可能多次调用上面的TSQL代码，但是。。。。。。如果服务器是链接服务器，有没有更有效的方法可以直接在TSQL中获取信息？只是好奇。

此操作的总体目的是用于部署目的，以查看所有服务器和数据库中存在多少过程。现在我们确实有来自Redgate的SQL比较，但我不知道它是否可以将不存在的proc脚本化为与集合A相同的存在。即使可以，如果可行的话，我也想尝试自己制作一些东西。

我们非常感谢您的帮助，如果您需要进一步澄清，请咨询。

Answer 1

我发现，一旦设置了链接服务器，就可以将链接服务器名称扩展到对象的左侧，以更清楚地限定它。

EG而不是sp_msforeachdb我可以做（Servername）.MASTER..sp_msforeach db.然后，如果我的服务器是sys.servers表中的链接（在我的情况下是链接），我可以遍历它们。

我用左联接做了一些会减慢速度的事情，我一次存储所有内容，然后用"like"语句而不是显式限定符进行检查。但总的来说，我认为这个解决方案将为最终用户提供灵活性，使其不知道要搜索的对象的确切名称。我还喜欢我现在可以将其与SSIS、SSRS和ADO.NET一起使用，因为该过程可以为我完成搜寻迭代，而且我不必在应用程序内存中做任何事情，而是在SQL服务器上。我相信其他人可能有更好的想法，但我没有听到任何消息，所以这是我的：

完整的解决方案如下：

Create PROC [PE].[DeployChecker]
    (
        @DB     VARCHAR(128)
    ,   @Proc   VARCHAR(128) 
    )
AS 
    BEGIN 
    --declare variable for dynamic SQL
    DECLARE 
        @SQL    VARCHAR(512)
    ,   @x      int
    -- remove temp table if it exists as it should not be prepopulated.
    IF object_ID('tempdb..#Procs') IS NOT NULL
        DROP TABLE tempdb..#Procs
    -- Create temp table to catch built in sql stored procedure
    CREATE TABLE #Procs --DECLARE @Procs table 
        (
            ServerName      varchar(64)
        ,   DatabaseName    VARCHAR(128)
        ,   ObjectName      VARCHAR(256)
        )
    SET @X = 1
    -- Loops through the linked servers with matching criteria to examine how MANY there are.  Do a while loop while they exist.
    -- in our case the different servers are merely incrementing numbers so I merely do a while loop, you could be more explicit if needed.
    WHILE @X <= (SELECT count(*) FROM sys.servers WHERE name LIKE 'PCTRSQL_')
    BEGIN
        -- for some reason I can't automate the 'sp_msforeachdb' proc to take dynamic sql but I can set a variable to do it and then run it.
        SET @SQL = 'Insert Into #Procs Exec PCTRSQL' + CAST(@X AS VARCHAR(2)) + '.MASTER..sp_msforeachdb ' +
        '''select @@SERVERNAME, ''''?'''', name from [?].sys.procedures (nolock) where ''''?'''' like ''''%' + @DB + '%'''' '''
        Exec (@SQL)
        SET @X = @X + 1
    END
    ;
    -- Find distinct Server detail 
    WITH s AS 
        (
        SELECT Distinct
            ServerName
        ,   DatabaseName
        FROM #Procs 
        )
    -- do logic search in the select statement to see if there is a proc like what is searched for
    , p AS
        (
        SELECT 
            ServerName
        ,   DatabaseName
        ,   CASE WHEN ObjectName LIKE '%' + @Proc + '%' THEN ObjectName END AS ProcName
        FROM #Procs
        where  ObjectName LIKE '%' + @Proc + '%'
        )
    --  now do a left join from the distinct server cte to the lookup for the proc cte, we want to examine ALL the procs that match a critera
    --  however if nothing eixsts we wish to show a NULL value of a single row for a reference to the Servername and Database
    SELECT 
        s.ServerName
    ,   s.DatabaseName
    ,   p.ProcName
    ,   CAST(CASE WHEN ProcName IS NOT NULL THEN 1 ELSE 0 END AS bit) AS ExistsInDB
    FROM s
        LEFT JOIN p ON s.ServerName = p.ServerName 
            AND s.DatabaseName = p.DatabaseName
        ORDER BY DatabaseName, ServerName, ProcName
END