Better dependency checking

The dependency information in SQL Server has always been a bit shaky. It’s a hard problem for them to solve. Meanwhile, I think you can do a lot worse than simply search the ‘source code’ of the objects in the database. That way you can even search for things that aren’t proper objects, such as column names or in fact any string at all. All you need are two fairly simple stored procedures in your master database.

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)

--print ''
--print ''
--print 'database: ' + @DBName



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'

--print @SQL

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 + '%'''

--print @SQL

exec sp_executesql @sql

set nocount off

go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s