MsSQL Find Usages Table Or Stored Procedure

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

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]

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：鹰潭站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!