sql 查找表引用的存储过程

 1 USE [master]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[uspGetDepends]    Script Date: 05/12/2016 14:11:36 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER proc [dbo].[uspGetDepends](
 9     @StrName varchar(100),
10     @Type varchar(10) = 'all'
11 )
12 as
13 begin
14     set nocount on
15     
16     --dbo.uspgetdepends 'tableName'
17 
18     --查询非用户表，即函数、存储过程、视图、约束等等。
19     select schema_name(schema_id) as [schema],* 
20     from sys.objects 
21     where object_id in(
22         select id from syscomments 
23         where [text] like '%'+@StrName+'%'  
24             and patindex('%201[0-9]%',[name]) = 0 --排除名字中包含年份的备份数据库对象
25     ) and (@type = 'all' or [type] = @type)
26 
27     union all
28     --查询用户自定表。
29     select schema_name(schema_id) as [schema],* 
30     from sys.objects
31     where [name] like '%'+@StrName+'%' 
32         and patindex('%201[0-9]%',[name]) = 0 --排除名字中包含年份的备份数据库对象
33         and [type] ='U' and ( @type='all' or @type = 'U')
34 
35     order by [name] 
36     set nocount off
37 end

 

转载于:https://www.cnblogs.com/ziranquliu/p/5485492.html