MS SQL - Find Stored Procedure (Dynamic) Related to Table in Database I have stored procedures like this:

CREATE PROC spSample (@departmentId INT)
AS

DECLARE @q NVARCHAR(500)

SET @q = N'SELECT * FROM Employees WHERE 1=1'
IF @departmentId &gt; 0
BEGIN
  SET @q = @q + ' AND DepartmentId = @departmentId'
END

EXECUTE sp_executesql @q, '@deparmentId INT', @departmentId


It is possible to find all related SPs like this one to the table "Employees"?
For dynamic SQLs, the only way to me is to search the definition of the stored procedure, like the following

use [your db]
select * from sys.sql_modules
where definition like '%Employee%'

-- if you know the pattern, you may use more restrictive way like
select * from sys.sql_modules
where definition like '% from Employee%'


But I have to say this may not be accurate. For example, if 'select * from Employee' is commented out in your SP, it will still be reported using this method.

Hope this helps.