SQL Query to find a schema used in all stored procs of a given database along with the columns

Question

Hello All,

I am looking for a query where I can input a schema name in the where clause and it would return me all the stored procs, views where this schema has been referenced along with all the columns that are being used from the table in that schema.

For example below is my stored proc:

Create proc dbo.Test 
AS

Select A.loan_number,B.PaymentAmount
From dbo.LoanTable A
Inner Join ( Select Loan_Number,PaymentAmount 
From loans.Paymenttable
Where Paidinfull = 0 ) B 
On A.Loan

Now, i would like to have a query where my input would be "loans" and it will return me the the stored procedure name "dbo.test" table name within it such as "PaymentTable" along with the columns that are being used such as "Loan_Number, paymentAmount, paidinfull".

Can this be achieved if so can someone please help me with this.

Thanks.

Answer 1

Alas, this is not as simple as it should be. There is a catalog view sys.sql_expression_dependecies, which in touted to give you the information you need. But there is a lot of ifs and buts. On my web site I have an article Where is that Table Used which discusses these ifs and buts and looks at alternatives.

Answer 2

Hi @sujith kumar matharasi

What you want to achieve is not as simple as searching for keywords in a table, it needs to involve more and more complexity.

As Erland Sommarskog says, You can learn about sys.sql_expression_dependecies.

You can also refer to the content in this link.[https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Answer 3

Thanks Erland & Percy, i used the link and worked on it to get what i needed.