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替换成想要检查的表名即可。