usp_depends - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

usp_depends

Author

Topic

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-22 : 11:01:57

SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON
GO

IF OBJECT_ID('dbo.usp_depends') IS NULL
EXEC ('CREATE PROC dbo.usp_depends
AS RAISERROR ( ''Dummy Proc Called'', 16, 62)')
GO
-- Modified verssion of sp_depends Tim S
ALTER PROC dbo.usp_depends
(
@objname nvarchar(766),
@yes_only bit = 0
) AS
BEGIN

DECLARE
@lookup_obj_name sysname,
@col_name sysname,
@dbname sysname,
@dbowner sysname,
@objid int,
@local_obj_name nvarchar(766)

-- Make sure the object is local to the current database.

SELECT @dbname = parsename(@objname,4)
SELECT @dbowner = parsename(@objname,3)

IF @dbname is not null and @dbname <> db_name()
BEGIN
PRINT 'use owner.table.column'
RAISERROR('The database name component of the object qualifier must be the name of the current database.',-1,-1)
RETURN (1)
END

SELECT @lookup_obj_name = PARSENAME(@objname, 2)
IF @lookup_obj_name IS NULL
BEGIN
SELECT @lookup_obj_name = PARSENAME(@objname, 1)
END
ELSE
BEGIN
SELECT @col_name = RTRIM(PARSENAME(@objname, 1))
END

-- See if object exists.
SELECT @local_obj_name = COALESCE( ('[' + @dbowner + '].'),'') + '[' + @lookup_obj_name + ']'
SELECT @objid = OBJECT_ID(@local_obj_name )
IF @objid IS NULL
BEGIN
PRINT 'use owner.table.column'
SELECT @dbname = db_name()
RAISERROR('The object ''%s'' does not exist in database ''%s''.',-1,-1,@local_obj_name,@dbname)
RETURN (1)
END

IF @col_name IS NOT NULL AND
( 0 = ( SELECT COUNT(*) FROM syscolumns WHERE id = @objid AND name = @col_name ) )
BEGIN
PRINT 'use owner.table.column'
SELECT @dbname = db_name()
RAISERROR('The column ''%s'' does not exist in object ''%s''.',-1,-1,@col_name,@local_obj_name)
RETURN (1)
END

SELECT X1.name, X1.updated, X1.selected, X1.type, @lookup_obj_name as obj_name, @col_name as column_name
FROM
(
SELECT DISTINCT
substring((s.name + '.' + o.name), 1, 40) AS 'name',
(
SELECT substring(u4.name, 1, 7)
FROM master.dbo.spt_values u4
WHERE u4.type = 'B' AND u4.number = d.resultobj
) AS updated,
(
SELECT SUBSTRING(w5.name, 1, 8)
FROM master.dbo.spt_values w5
WHERE w5.type = 'B' and w5.number = d.readobj|d.selall
) AS selected,
col_name(d.depid, d.depnumber) AS 'column',
o.type
FROM sysobjects o
JOIN sysusers s
ON o.uid = s.uid
JOIN sysdepends d
ON o.id = d.id
JOIN master.dbo.spt_values v
ON o.xtype = substring(v.name,1,2) and v.type = 'O9T'
WHERE d.depid = @objid AND
( col_name(d.depid, d.depnumber) = @col_name OR @col_name IS NULL)
) AS X1
WHERE ( @yes_only = 0 OR X1.updated = 'yes' OR X1.selected = 'yes')

RETURN (0)
END

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-22 : 11:05:30

Note: I use this proc to find out which procedure use a table column.

Tim S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources