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.
SQL Query to find a schema used in all stored procs of a given database along with the columns
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.
Sign in to comment
2 additional answers
Sort by: Most helpful
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
If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".
Thanks Erland & Percy, i used the link and worked on it to get what i needed.