MsSQL Find Usages Table Or Stored Procedure
程序员文章站
2022-06-04 21:57:50
...
今天在修改數隻(是這個隻還是這個支啊 '_')一、二年前撰寫的storedprocedure(T-SQL)後發現, 要找出這些被異動的SP曾在那些的SP裡面有使用,或者某張資料表在那幾隻SP內使用,這還真是一件苦差事。 此時突然想起Visual Studio 內有個功能叫 FindUsages ,可
今天在修改數隻(是這個"隻"還是這個"支"啊 '_>') 一、二年前撰寫的stored procedure(T-SQL)後發現,
要找出這些被異動的SP曾在那些的SP裡面有使用,或者某張資料表在那幾隻SP內使用,這還真是一件苦差事。
此時突然想起 Visual Studio 內有個功能叫 FindUsages ,可以方便的找出Function 曾在那些程式中使用,
花了一個上午的時間,寫出功能相仿的T-SQL版的 FindUsages
SQL ServerCREATE 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]
下一篇: PHP+Ajax无刷新登录与退出