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%'

19 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
  5. Awesome article! I want people to know just how good this information is in your article. It’s interesting, compelling content. Your views are much like my own concerning this subject. review

    ReplyDelete

  6. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course
    Msbi course
    Msbi training
    Msbi certification training

    ReplyDelete
  7. Este é um ótimo recurso que você está fornecendo e distribuindo gratuitamente. Adoro ver blogs que entendem o valor de fornecer um recurso de qualidade gratuitamente. ver mensagens enviadas do telemovel

    ReplyDelete
  8. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.. 웹툰보기

    ReplyDelete
  9. Just want to say your article is as astonishing. The clarity in your post is simply nice and i could assume you’re an expert on this subject. Well with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the gratifying work. Best Assignment Help UK

    ReplyDelete
  10. FreeAssignmentHelp is the World’s Best Online Assignment help, Academic Homework Help and writing assistance web provider that prides itself in being the best online assignment help website for students around the world.
    Get Connected With Our 450+ Academic Experts To Receive Custom Assignment Solutions Along With Plagiarism Reports For ScorinG good Marks, Not only this, even if you do not require a typically Do My Assignment For Me kind of service and simply need expert consultation and writing help on assignment writing styles, assignment writing services
    Management Accounting Assignment Help Uk

    Financial Accounting Assignment Help Uk

    Accounting Lease Critical Review Assignment Help Uk

    Database Design Concept Assignment Help Uk

    Forensic Accounting Bernard Assignment Help

    ReplyDelete
  11. Software engineering Assignment help concerns methods and techniques to develop large software systems. The engineering metaphor is used to emphasize a systematic approach to develop systems that satisfy organizational requirements and constraints. This chapter gives a brief overview of the field and points at emerging trends that influence the way software is developed.
    Get Connected With Our 450+ Academic Experts To Receive Custom Assignment Solutions Along With Plagiarism Reports For ScorinG good Marks, Not only this, even if you do not require a typically Do My Assignment For Me kind of service and simply need expert consultation and writing help on assignment writing styles, assignment writing guides and need free study notes to consult for your essay research writing on hundreds of topics, including Economics Statistics, Business, Management, Finance, Accounts, History,

    ReplyDelete
  12. Essays are the most frequent academic assignments assigned to students. It is short in length but requires more concentration, effort and time. To help you prepare a quality essay, our Essay Assignment Help Canada provides all the support you need. Our customized essay help services in Canada cover all types of essays, even admissions essays and scholarship essays.

    ReplyDelete
  13. When you are making travel plans make a list of all the things you need in your rental car and all of your preferences so that you can be sure the car rental agency you choose has what you want. With a little advance planning you can get exactly the car you need to have a great time on your vacation. Luxury Car Rental Miami

    ReplyDelete