- 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://www.codesnippet.cn/detail/290320132618.html
来源: http://www.codesnippet.cn/detail/290320132618.html