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
