CREATE PROCEDURE sam_proc_FindTableProcedure
@objname VARCHAR(1000)
AS
DECLARE @objid INT
DECLARE @found_some bit
DECLARE @dbname sysname
SELECT @dbname = parsename(@objname,3)
IF @dbname IS NOT NULL AND @dbname <> db_name()
BEGIN
raiserror(15250,-1,-1)
RETURN (1)
end
SELECT @objid = object_id(@objname)
IF @objid IS NULL
BEGIN
SELECT @dbname = db_name()
raiserror(15009,-1,-1,@objname,@dbname)
RETURN (1)
end
SELECT @found_some = 0
SET NOCOUNT ON
IF exists (SELECT *
FROM sysdepends
WHERE id = @objid)
BEGIN
RAISERROR(15459,-1,-1)
SELECT 'name' = (s6.name+ '.' + o1.name),
type = substring(v2.name, 5, 66),
updated = substring(u4.name, 1, 7),
selected = substring(w5.name, 1, 8),
'column' = col_name(d3.depid, d3.depnumber)
FROM sys.objects o1
,master.dbo.spt_values v2
,sysdepends d3
,master.dbo.spt_values u4
,master.dbo.spt_values w5
,sys.schemas s6
WHERE o1.object_id = d3.depid
AND o1.type = 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.schema_id = s6.schema_id
AND deptype < 2
SELECT @found_some = 1
END
IF exists (SELECT *
FROM sysdepends
WHERE depid = @objid)
BEGIN
raiserror(15460,-1,-1)
SELECT distinct 'name' = (s.name + '.' + o.name),
type = substring(v.name, 5, 66)
FROM sys.objects o, master.dbo.spt_values v, sysdepends d,
sys.schemas s
WHERE o.object_id = d.id
AND o.type = substring(v.name,1,2) collate database_default AND v.type = 'O9T'
AND d.depid = @objid
AND o.schema_id = s.schema_id
AND deptype < 2
SELECT @found_some = 1
END
IF @found_some = 0
RAISERROR(15461,-1,-1)
SET NOCOUNT OFF
RETURN (0)