How to Find if Table/View is used by Stored Procedure in SQL Server - SQL Server Interview Questions

In this video, we are going to learn how to find out if the table is used in a Stored Procedure. we have multiple ways to do that.

This System Stored Procedure will return you list of table/views used by the Stored procedure/View. You can pass the view name or Stored Procedure as parameter,it will return you tables/views which are used in the object.

One disadvantage of using sp_depends is , it will not show you tables/views which are not in current database. If you have used objects from other databases, this information might be misleading.

--By using sp_depends, You can provide Stored Procedure Name or View name as parameter
EXEC sp_depends '[dbo].[GetCustomer]'

SELECT DISTINCT OBJECT_NAME(SD.id) AS StoredProcedureNameName,
OB.name AS TableOrViewName
ON SD.id=O.id
ON SD.depid=OB.id
AND O.xtype='P'

--in a Stored Procedure
SELECT OBJECT_NAME(OBJECT_ID),definition
WHERE definition LIKE '%vw_Employee%'

Link to post:

[krismaly]

This is an important video for all developers who works on tables and views. I like the way narrator explained and recommend others to watch.

Thanks for educating the community.

[supersql]

Love the real-world critical knowledge! Thank you!! and Subscribed!

[trolldicker]

There is also a sys.comments table that has definition of all stored procedures

[krismaly]

If the Stored procedure used tables or views of multiple databases then don't use sp_depends
OR 
Sys.sysdepends
Sys.Sysobjects 
and create a select statement
but use sys.all_sql_modules and analyze

Click to view the script for this video
How to find if Table/View is used in Stored Procedure in SQL Server


Thanks a lot

[architaguha]

How to verify if a table is successfully rotated or not?