How to find if Table/View is used in Stored Procedure in SQL Server

In this post, 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]'



The 2nd method is to use the sys.sysdepends view with combination of sys.sysobjects to get the information. This also works the same way. It will not show you cross database objects ( Tables,Views) if have used in Stored Procedure or view definition.

--Get Information from sys.sysdepends 
SELECT DISTINCT OBJECT_NAME(SD.id) AS StoredProcedureNameName,
 OB.name AS TableOrViewName
 FROM sys.sysdepends SDINNER JOIN sys.sysobjects O 
ON SD.id=O.id
 INNER JOIN sys.sysobjects OB 
ON SD.depid=OB.id 
AND O.xtype='P'


3rd method is to use the sys.all_sql_modules system view. The definition column of this view has definition of Stored Procedure, Views, Functions etc.You can write query as given below to check if any of the Stored Procedure or object is using the table/view you are looking for.

--Use sys.all_sql_modules system view to get information if table is used
--in a Stored Procedure
 
SELECT OBJECT_NAME(OBJECT_ID),definition 
 FROM sys.all_sql_modules 
WHERE definition LIKE '%vw_Employee%'

7 comments:

  1. Sales reps consistently attempt to settle on you settle on a choice to purchase now, and will attempt to convince you to settle on an immediate arrangement.Used Cars

    ReplyDelete
  2. This data and some other required data will be provided in the car fix manual. Car Tire Repair Shop

    ReplyDelete
  3. Thank for this blog more informative step by step and useful content. I here by attached my site would see this blog

    7 tips to start a career in digital marketing

    “Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

    we have offered to the advanced syllabus course digital marketing for available join now

    more details click the link now

    https://www.webdschool.com/digital-marketing-course-in-chennai.html

    ReplyDelete
  4. Amazing blog very nice

    Web designing trends in 2020

    When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

    we have offered to the advanced syllabus web design and development for available join now

    more details click the link

    https://www.webdschool.com/web-development-course-in-chennai.html

    ReplyDelete