SQLServer 检查引用了特定数据表的所有存储过程

1. 方法一：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v6.0.70
 * Time: 2012/11/21 23:13:33
 ************************************************************/

SET NOCOUNT ON
DECLARE @T_objs TABLE
        (objname VARCHAR(1000))

DECLARE @T_objText TABLE
        (objname VARCHAR(MAX))
 
DECLARE @T_result TABLE
        (objname VARCHAR(MAX))

INSERT INTO @T_objs
SELECT b.[name] + '.' + a.[name]
FROM   sysobjects a
       JOIN sys.schemas b
            ON  a.[uid] = b.[schema_id]
WHERE  b.name = 'ILG'
       AND (xtype = 'P' OR xtype = 'fn' OR xtype = 'v')
--insert into @T_objs select 'ILG.'+[name] from sysobjects where UID=6 AND (xtype='P' OR xtype='fn' OR xtype='v')
DECLARE @name NVARCHAR(1000)
DECLARE @sqlText VARCHAR(MAX)
SET @sqlText = ''
SET @name = ''
SELECT TOP 1 @name = objname
FROM   @T_objs

WHILE @@rowcount > 0
BEGIN
    INSERT @T_objText
    EXEC sp_helpText @name
   
    SELECT @sqlText = @sqlText + objname
    FROM   @T_objText
   
    DELETE @T_objText
    INSERT INTO @T_objText
    SELECT REPLACE(
               REPLACE(REPLACE(@sqlText, CHAR(13) + CHAR(10), ''), '', ''),
               '',
               ''
           )
    --print replace(replace(replace(@sqlText,char(13)+char(10),''),'',''),'','')
    IF EXISTS(
           SELECT *
           FROM   @T_objText
           WHERE  objname LIKE '%TableName%'
       ) --多条件控制
    BEGIN
        INSERT INTO @T_result
        SELECT @name
    END
   
    SET @sqlText = ''
    DELETE @T_objs
    WHERE  @name = objname
   
    DELETE @T_objText
    SELECT TOP 1 @name = objname
    FROM   @T_objs
END
SELECT *
FROM   @T_result
      

2. 方法二：

SELECT *
FROM   sysobjects s
       INNER JOIN syscomments s2
            ON  s.id = s2.id
WHERE  s2.text LIKE '%TableName%'

 

将TableName替换成想要检查的表名即可。

 

转载于:https://www.cnblogs.com/shuzehui/archive/2012/11/09/2763167.html