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
2023-01-12T22:16:24.7666667+00:00

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.

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

Accepted answer
  1. Erland Sommarskog 68,196 Reputation points Microsoft MVP
    2023-01-12T22:30:14.5333333+00:00

    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: Most helpful
  1. PercyTang-MSFT 1,346 Reputation points Microsoft Employee
    2023-01-13T05:19:41.3033333+00:00

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

    No comments

  2. sujith kumar matharasi 351 Reputation points
    2023-01-17T15:26:17.02+00:00

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

    No comments