1

Basically I ve read a couple of questions regarding this matter on StackOverflow and there have been a lot of different opinions regarding the right answer. Could someone give a definitive answer to this , at least when it comes to SQL Server 2008 and upwards?

Also the reason why I am asking is because I would like to input the name of a table or field (table column) which would result in listing all the stored procedures with a dependency on the value entered . How would I go about doing this? This is my code so far.

SELECT *
     FROM sys.sql_dependencies d
INNER JOIN sys.objects o
      ON d.object_id = o.object_id
INNER JOIN sys.objects p
      ON d.referenced_major_id = p.object_id
WHERE o.type_desc='SQL_STORED_PROCEDURE' and o.name LIKE 
'%%' AND p.name LIKE '%%';
AC007
  • 137
  • 9
  • 1
    I would suggest using [`sys.sql_expression_dependencies`](https://msdn.microsoft.com/en-us/library/bb677315.aspx) – GarethD Oct 15 '15 at 10:50
  • I ve tried using `sys.sql_expression_dependencies`and I was wondering how I would view the tables and the table_columns of my database? I ve already narrowed down my search with `WHERE o.type_desc='SQL_STORED_PROCEDURE'` – AC007 Oct 15 '15 at 11:03
  • I managed to find the tables being used , however I m not sure how i would access the columns found in each table. Also I have yet to find the names of the stored procedures – AC007 Oct 15 '15 at 11:43
  • If your stored procedure use Dynamic-SQL it will be very difficult to get 100% reliable solution. – Lukasz Szozda Oct 15 '15 at 12:17
  • This is a duplicate of http://stackoverflow.com/questions/27654344/how-check-that-how-many-stored-procedure-will-affect-after-deleting-one-table-in/27676104#27676104. I here you can find all the different methods. – Amir Pelled Oct 15 '15 at 12:27

1 Answers1

0

So I found that this worked for me as it allowed me to search through any fields only through my stored procedures, hope this helps others:​ 

SELECT DISTINCT OBJECT_NAME(OBJECT_ID), object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + @SearchItem + '%' `
Fabian N.
  • 3,807
  • 2
  • 23
  • 46
AC007
  • 137
  • 9