sql 查找表引用的存储过程

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[uspGetDepends]    Script Date: 05/12/2016 14:11:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[uspGetDepends](
    @StrName varchar(100),
    @Type varchar(10) = 'all'
)
as
begin
    set nocount on
    
    --dbo.uspgetdepends 'tableName'

    --查询非用户表，即函数、存储过程、视图、约束等等。
    select schema_name(schema_id) as [schema],* 
    from sys.objects 
    where object_id in(
        select id from syscomments 
        where [text] like '%'+@StrName+'%'  
            and patindex('%201[0-9]%',[name]) = 0 --排除名字中包含年份的备份数据库对象
    ) and (@type = 'all' or [type] = @type)

    union all
    --查询用户自定表。
    select schema_name(schema_id) as [schema],* 
    from sys.objects
    where [name] like '%'+@StrName+'%' 
        and patindex('%201[0-9]%',[name]) = 0 --排除名字中包含年份的备份数据库对象
        and [type] ='U' and ( @type='all' or @type = 'U')

    order by [name] 
    set nocount off
end