Sql Dependencies


This is a rad sproc to find dependencies from Brett. Sometimes I come across a sproc that I *think* I can delete but you never can be too sure. This makes that process A LOT easier. I am reposting it here for my own benefit with some slight formatting differences to make it easier for me to read:

/* testing...

exec usp_depends2 ‘tblCustomers’

*/
CREATE procedure usp_depends2
@objname nvarchar(776) –the object we want to check

as

declare @objid int –the id of the object we want
,@found_some bit –flag for dependencies found
,@dbname sysname

–Make sure the @objname is local to the current database.

DECLARE @sp_depends_xref table (
reftype char(2)
, dep_name nvarchar(256)
, type char(16)
, updated char(7)
, selected char(8)
, [column] nvarchar(128)
)

select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

–See if @objname exists.
select @objid = object_id(@objname)
if @objid is null
begin
select @dbname = db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

–Initialize @found_some to indicate that we haven’t seen any dependencies.
select @found_some = 0

set nocount on

–Print out the particulars about the local dependencies.
if exists (select * from sysdepends where id = @objid)
begin
raiserror(15459,-1,-1)
INSERT INTO @sp_depends_xref (
refType
,dep_name
,type
,updated
,selected
,[column]
)
SELECT
‘TO’
,’name’ = (s6.name+ ‘.’ + o1.name)
,type = substring(v2.name, 5, 16)
,updated = substring(u4.name, 1, 7)
,selected = substring(w5.name, 1, 8)
,’column’ = col_name(d3.depid, d3.depnumber)
FROM
sysobjects o1
,master.dbo.spt_values v2
,sysdepends d3
,master.dbo.spt_values u4
,master.dbo.spt_values w5
,sysusers s6
WHERE
o1.id = d3.depid
and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = ‘O9T’
and u4.type = ‘B’ and u4.number = d3.resultobj
and w5.type = ‘B’ and w5.number = d3.readobj|d3.selall
and d3.id = @objid
and o1.uid = s6.uid
and deptype < 2

select @found_some = 1
end

–Now check for things that depend on the object.
if exists (select * from sysdepends where depid = @objid)
begin
raiserror(15460,-1,-1)

INSERT INTO @sp_depends_xref (
RefType
,dep_name
,type
)
SELECT DISTINCT
‘BY’
,’name’ = (s.name + ‘.’ + o.name)
,type = substring(v.name, 5, 16)
FROM
sysobjects o
,master.dbo.spt_values v
,sysdepends d
,sysusers s
WHERE
o.id = d.id
and o.xtype = substring(v.name,1,2) collate database_default and v.type = ‘O9T’
and d.depid = @objid
and o.uid = s.uid
and deptype < 2

select @found_some = 1
end

–Did we find anything in sysdepends?

if @found_some = 0
raiserror(15461,-1,-1)

SELECT
reftype
,dep_name
,type
,updated
,selected
,[column]
FROM
@sp_depends_xref

set nocount off

return (0)

[Listening to: Postmortem – Slayer (3:27)]
,

One response to “Sql Dependencies”

  1. Hey, I’m flattered.

    But what about the rest of it. You need to leverage the new sproc to mine the data. It also will let you build family tree relationships of dependancies…

    Anyway, thanks for the plug.

Leave a Reply

Your email address will not be published. Required fields are marked *