A better INFORMATION_SCHEMA.ROUTINES
FYI: INFORMATION_SCHEMA.ROUTINES
only pulls the first 4000 chars of a stored procedure.
Sometimes procs are longer so my original select missed some of the tables.
TOC
The improved ROUTINES routine
Here is the new sql to match tables to columns:
WITH [ROUTINES]
AS (SELECT SCHEMA_NAME(o.schema_id) AS ROUTINE_SCHEMA,
o.name AS ROUTINE_NAME,
CONVERT(NVARCHAR(20),
CASE
WHEN o.type IN('P', 'PC')
THEN 'PROCEDURE'
ELSE 'FUNCTION'
END) AS ROUTINE_TYPE,
CONVERT(NVARCHAR(MAX), OBJECT_DEFINITION(o.object_id)) AS ROUTINE_DEFINITION
FROM sys.objects AS o
LEFT JOIN
sys.parameters AS c
ON c.object_id=o.object_id
AND c.parameter_id=0
WHERE o.type IN
('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS'))
SELECT r.ROUTINE_TYPE,
r.ROUTINE_SCHEMA+'.'+r.ROUTINE_NAME AS ROUTINE,
t.TABLE_SCHEMA+'.'+t.TABLE_NAME AS [TABLE],
PATINDEX('%'+t.TABLE_NAME+'%', UPPER(r.ROUTINE_DEFINITION))
FROM [ROUTINES] AS r
INNER JOIN
INFORMATION_SCHEMA.TABLES AS t
ON r.ROUTINE_DEFINITION LIKE '%'+t.TABLE_NAME+'%'
ORDER BY 1, 2, 3;
GO
the CTE gets its ‘ROUTINES list from:
sp_helptext [INFORMATION_SCHEMA.ROUTINES]
Credits
If you find this useful, go be nice to someone. Pay it forward.
Cheers!