if exists ( select 1
from dbo.sysobjects
where id = object_id(N'dbo.dba_depends')
and objectproperty(id, N'IsProcedure') = 1 )
drop procedure dbo.dba_depends
go
create proc dbo.dba_depends
(
@SearchTerm varchar(255)
, @IncludeSystemDBs bit = 0
)
as
/*
-------------------------------------------------------------------------------
Version : 1.5
Date : 07/11/2007
Comments : syscomments.xtype
-----------------
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
-------------------------------------------------------------------------------
*/
declare @DBName varchar(128)
, @SearchThisDB bit
create table #t
(
DBName varchar(128) null
, ObjectName sysname null
, ObjectXType char(2) null
)
declare db_cursor cursor
local forward_only
for
select [name]
from sys.databases
where state = 0 --ONLINE
order by name
open db_cursor
fetch next
from db_cursor
into @DBName
while @@fetch_status = 0
begin
set @SearchThisDB = 1
if @IncludeSystemDBs = 0 and lower(@DBName) in ('master','tempdb','model','msdb','reportserver','reportservertempdb')
set @SearchThisDB = 0
if @SearchThisDB = 1
exec master.dbo.dba_depends_inner @SearchTerm, @DBName
fetch next
from db_cursor
into @DBName
end
close db_cursor
deallocate db_cursor
select distinct
DBName
, case
when ObjectXType = 'FN' then 'Function'
when ObjectXType = 'P' then 'Stored Procedure'
when ObjectXType = 'IF' then 'Inlined table-function'
when ObjectXType = 'TF' then 'Table function'
when ObjectXType = 'TR' then 'Trigger'
when ObjectXType = 'V' then 'View'
when ObjectXType = 'X' then 'Extended stored procedure'
when ObjectXType = 'U' then 'Table'
else ObjectXType
end as ObjectType
, ObjectName
, ObjectXType
from #t
order by 1,2,3
drop table #t
go
if exists ( select 1
from dbo.sysobjects
where id = object_id(N'dbo.dba_depends_inner')
and objectproperty(id, N'IsProcedure') = 1 )
drop procedure dbo.dba_depends_inner
go
create proc dbo.dba_depends_inner
(
@SearchTerm varchar(255)
, @DBName varchar(128)
)
as
/*
-------------------------------------------------------------------------------
Version : 1.3
Date : 12/03/2007
-------------------------------------------------------------------------------
*/
set nocount on
declare @SQL nvarchar(500)
set @SQL = 'use [' + @DBName + '] ' +
'insert #t
(DBName
, ObjectName
, ObjectXType)
select ''' + @DBName +
''' , so.[name]
, so.xtype
from syscomments sc
inner join sysobjects so on so.[id] = sc.[id]
where so.xtype in (''FN'', ''P'', ''IF'', ''TF'', ''TR'', ''V'', ''X'')
and sc.[text] like ''%' + @SearchTerm + '%''
order by sc.[id], sc.colid'
exec sp_executesql @sql
set @SQL = 'use [' + @DBName + '] ' +
'insert #t
(DBName
, ObjectName
, ObjectXType)
select ''' + @DBName +
''' , obj.name + ''.'' + col.name
, ''U''
from sysobjects obj
inner join syscolumns col
on obj.id = col.id
where obj.xtype in (''U'')
and col.name like ''%' + @SearchTerm + '%'''
exec sp_executesql @sql
set nocount off
go