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

sujith kumar matharasi 351 Reputation points

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 

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.


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,041 questions
No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 72,226 Reputation points MVP

    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.

2 additional answers

Sort by: Newest
  1. sujith kumar matharasi 351 Reputation points

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

  2. PercyTang-MSFT 2,921 Reputation points Microsoft Vendor

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

    1 person found this answer helpful.
    No comments