[SQL] Tracking down SP dependancies

Scenario: There’s a stored procedure that runs daily, but there’s no information on what executes the stored procedure.


SELECT
getdate() as EventDate,
DB_NAME() As dbname,
HOST_NAME() As hostname,
APP_NAME() as applicationname,
OBJECT_NAME(@@PROCID) as procedurename,
USER_ID() as userid,
USER_NAME() as unsername,
SUSER_ID() as suserid,
SUSER_SNAME() as susername,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address as ipaddress
from sys.dm_exec_connections
where session_id = @@spid

SELECT TOP 100 *
from INFORMATION_SCHEMA.ROUTINES
--WHERE specific_schema = 'YourSchema'
WHERE ROUTINE_DEFINITION LIKE '%YourObject%'

--Returns one row for each stored procedure and function
--that can be accessed by the current user in the current database.
--The columns that describe the return value apply only to functions.
--For stored procedures, these columns will be NULL.

SELECT
distinct so.*
--, '#' , sc.*
from syscomments sc
JOIN sysobjects so ON so.id = sc.id
where sc.text like '%YourObject%'

SELECT TOP 10 *
FROM reporting.sys.sql_modules
WHERE charindex('YourObject',definition)>0;

(The text column in syscomments has a limit of nvarchar(4000) and SPs with definitions greater than that size will be trimmed to that limit...the definition column in sys.sql_modules is nvarchar(max))

SELECT TOP 10 *
FROM msdb..sysjobsteps

sp_depends 'YourSchema.YourObject'