2

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 > 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"?

4

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.

1
1

You want to check out: Redgate - SQL Search

It is a free plugin for SSMS to search for text in the SQL Server.
It searches in all kind of different objects (Tables, Views, Stored Procs, Agent Jobs, ...) in a convenient way.

1
  • Thank you! but it seems that it only finds the normal SPs.
    – Middleman
    Feb 11 '18 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.