Hi Folks ,
Continuing from my last post ,In which we are finding various relationship with table and stored procedure
BISQL # 93 – How to find list of Stored procedure which does not depend on any table operation : In this post we have learn how to find stored procedure which is not participating and Stored procedure table relationship
Also to be in synch each other my one of previous article (How to establish / Find relationship between Table and stored Procedure in Database) in which we have already covered following points :
How to analyse all schema
How to analyse all Tables in Database
How to analyse all procedure
How to analyse all dependency Between Table and Stored Procedure
Establishing Relation Between Table and Stored Procedure
Final and Quick result script for Relationship of Table and Stored Procedure
In this article we are going to cover following problem statement
Problem : How to find tables which is not participating in Stored procedure table relationship
Lets do some querying to get expected result first on Adventure work DB
Following query will provide us list of all tables in given Database system
SELECT * FROM sys.tables
Which have following in output as 70 tables
Now lets fire query which we have already discuss : Final and Quick result script for Relationship of Table and Stored Procedure
p.name AS 'Stored Procedure' ,
s.name AS 'Schema Name',
t.name AS 'Table Name'
FROM sys.sysdepends AS d
INNER JOIN sys.procedures AS p
ON d.id = p.object_id
INNER JOIN sys.tables AS t ON t.object_id = d.depid
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id;
Which shows output as in which stored procedure and which table have been used interns of table – Stored procedure relation
Now here what our problem requirement is in which list of Table which are not dependent on stored procedure.
Which means only 6 tables are used by stored procedure !
We can clearly see n this output and output compare to just above script stored procedure does not contain any table in its definition which are in second output result.
Hope you will like this post on How to find tables which is not participating in Stored procedure table relationship.
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
Link Resource Website
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |