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%'
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%'
I really liked and I got some innovative ideas for improving my thoughts from well defined content.
ReplyDeleteIELTS Coaching in Chennai
IELTS Coaching centre in Chennai
French Classes in Chennai
pearson vue test center in chennai
Informatica Training in Chennai
Data Analytics Courses in Chennai
content writing course in chennai
IELTS Coaching in OMR
IELTS Coaching in Porur
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
ReplyDeleteGreat Article
ReplyDeleteData Mining Projects
Python Training in Chennai
Project Centers in Chennai
Python Training in Chennai
This data and some other required data will be provided in the car fix manual. Car Tire Repair Shop
ReplyDeleteThank for this blog more informative step by step and useful content. I here by attached my site would see this blog
ReplyDelete7 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
Amazing blog very nice
ReplyDeleteWeb 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
Thanks for the infomative article.
ReplyDeleteSQL Server Hosting in chennai
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
ReplyDeleteThrough 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
Clipping Xpert
ReplyDeleteClipping Xpert India
Paragon Clipping Path
Clipping Path Service
Image Editing Company
Ecommerce Image Editing Service
Clipping path company
Clipping Path Service
Image Editing Company
Ecommerce Image Editing Service
Clipping path company
Clipping Path Service
Image Editing Company
Ecommerce Image Editing Service
Clipping path company
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
ReplyDeleteSuperbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.. 웹툰보기
ReplyDelete