返回顶部

收藏

MsSQL Find Usages Table Or Stored Procedure

更多

今天在修改?脞(是呃?"脞"?是呃?"支"啊 '_>') 一、二年前撰?的stored procedure(T-SQL)後办?,

要找出呃些被??的SP曾在那些的SP彦面有使用,或者某?儋料表在那?脞SP?使用,呃?真是一件苦差事。

此?突然想起 Visual Studio ?有?功能叫 FindUsages ,可以方便的找出Function 曾在那些程式中使用,

花了一?上午的?殓,?出功能相仿的T-SQL版的 FindUsages

CREATE PROCEDURE [dbo].[sp_FindUsages]
    (@ObjectName SYSNAME)  
AS
BEGIN  
    SET NOCOUNT ON;
    CREATE TABLE #Result([Id] INT IDENTITY, [ObjectName] VARCHAR(100), [Line] INT,[Text] NVARCHAR(max));

    DECLARE @Id INT;
    DECLARE @Line INT;
    DECLARE @Name NVARCHAR(1024);

    DECLARE pl CURSOR FOR SELECT [name] FROM sys.procedures;
    OPEN pl;
    FETCH NEXT FROM pl INTO @Name;
    WHILE @@FETCH_STATUS = 0 
    BEGIN      
        EXEC ('INSERT INTO #Result ([Text]) EXEC sp_helptext ' + @Name);
        SET @line = 7;
        DECLARE r CURSOR FOR SELECT Id FROM  #Result WHERE ObjectName IS NULL;  
        OPEN r;
        FETCH NEXT FROM r INTO @Id;
        WHILE @@FETCH_STATUS = 0 
        BEGIN
            SET @Line = @line + 1;
            UPDATE #Result SET Line = @Line,ObjectName = @Name WHERE Id = @id ;
            FETCH NEXT FROM r INTO @Id;
        END
        CLOSE r;
        DEALLOCATE r;   
        FETCH NEXT FROM pl INTO @Name;
    END
    CLOSE pl;
    DEALLOCATE pl;
    SELECT * FROM #Result WHERE [Text] LIKE '%' + @ObjectName + '%';
    DROP TABLE #Result;
END

--exec [sp_FindUsages] [spName]
--该片段来自于http://outofmemory.cn

标签:sql,数据库

收藏

0人收藏

支持

0

反对

0

发表评论