find SP dependencies

SQL 2K
My db has a number of SP that are no longer in use. sp_depends (and Show
Dependencies from EM) sees hit and miss. Both tell me that usp_Begin has no
dependencies even though it contains this
ALTER PROCEDURE uspCreatePayerServiceDataFromTp3Data
(
@.CredID AS BIGINT,
@.BillerId AS BIGINT,
@.ErrorNumber AS INT OUTPUT,
@.Description AS VARCHAR(4000) OUTPUT)
AS
SET @.Description = ISNULL(@.Description,'')
EXEC @.ErrorNumber = /*SQLTRCLP*/TP3_TP4_Migration.dbo.usp_SelectAccountUsers
@.CredID, @.BillerId, @.Description OUTPUT
SET @.ErrorNumber = @.@.ERROR
RETURN @.ErrorNumber
and it only finds one of the three SPs called in
TP3_TP4_Migration.dbo.usp_SelectAccountUsers but all of the tables.
Is there a reliable way to determine what SP's truly depend on what othe
objects?
SQL Server is able to do this, becuase it barks if I misspell an object name
.
thanks
kevinOJ did a script.. as sp_depends has issues...
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
HTH. Ryan
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:6D68AE72-0B0C-434F-83EC-FBBF125A46F9@.microsoft.com...
> SQL 2K
> My db has a number of SP that are no longer in use. sp_depends (and Show
> Dependencies from EM) sees hit and miss. Both tell me that usp_Begin has
> no
> dependencies even though it contains this
> ALTER PROCEDURE uspCreatePayerServiceDataFromTp3Data
> (
> @.CredID AS BIGINT,
> @.BillerId AS BIGINT,
> @.ErrorNumber AS INT OUTPUT,
> @.Description AS VARCHAR(4000) OUTPUT)
> AS
> SET @.Description = ISNULL(@.Description,'')
> EXEC @.ErrorNumber =
> /*SQLTRCLP*/TP3_TP4_Migration.dbo.usp_SelectAccountUsers
> @.CredID, @.BillerId, @.Description OUTPUT
> SET @.ErrorNumber = @.@.ERROR
> RETURN @.ErrorNumber
> and it only finds one of the three SPs called in
> TP3_TP4_Migration.dbo.usp_SelectAccountUsers but all of the tables.
> Is there a reliable way to determine what SP's truly depend on what othe
> objects?
> SQL Server is able to do this, becuase it barks if I misspell an object
> name.
> thanks
> kevin