I'm looking for a way to see all what stored procedures impact specific tables. Is there a better way than using
select * from DatabaseName.information_schema.routines
where routine_type = 'PROCEDURE'
and ROUTINE_DEFINITION like '%TableName%'
This query will give you all the Stored Procedures that depend on table 'MyTable':
SELECT s.name as [ObjectName], t.name as [DependsOn] FROM sys.sql_expression_dependencies d INNER JOIN sys.objects s ON d.referencing_id = s.object_id INNER JOIN sys.objects t ON d.referenced_id = t.object_id WHERE t.name = 'MyTable' AND s.type = 'P'
You can alter the WHERE clause to be more specific, or less specific, or also add in functions, views, etc. if you wish.