April 10, 2008 at 3:02 am
Dear All,
How to find the number of table used in a procedure ?
----------------------------------------------------
Say for example,
Create procedure P1
(
@Flag char(1)
)
as
begin
Create table #P1
(
Eno int,
Ename varchar(25)
)
if @Flag = 'Y'
Begin
Insert into #P1
Select Eno,Name from Emp
End
if @Flag = 'N'
Begin
Insert into #P1
Select Eno,Name from Emp_History
End
if @Flag = 'Z'
Begin
Insert into #P1
Select Eno,Name from Old_Employees
End
if @Flag = 'R'
Begin
Insert into #P1
Select Eno,Name from Retired_Employees
End
if @Flag = 'A'
Begin
Insert into #P1
Select Eno,Name from Adult_Employees
End
.
.
.
.
.
.
.
End -- End of Procedure
---------------------------------
There are five tables used in this procedure.
1) Emp
2)Employee_History
3)Old_Employees
4)Retired_Employees
5)Adult_Employees
Let us assume, we have a procedure with more than 5000 lines and it uses more table,in this case, how do we find out the number of tables used by that procedure ?
Do we have work out manually to find out the table names ?
Thanks in advance!
karthik
April 10, 2008 at 3:13 am
I don't know exactly, but you can probably do something along the lines of...
select * from information_schema.tables
where table_type = 'base table' and table_name in (
select distinct object_name(referenced_major_id)
from sys.sql_dependencies
where object_id = object_id('P1'))
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 10, 2008 at 3:16 am
You can try and use exec sp_depends 'P1'
But that isn't necessarily accurate - read up on sp_depends.
The more accurate way of doing this is by right-clicking on the procedure in Management Studio and select "View Dependencies". Then select the Radio Button "Objects on which [P1] depends".
April 10, 2008 at 3:23 am
On SQL Server 2000 you can look at the dbo.sysdepends table, on SQL Server 2005 the sys.sql_dependencies.
Unfortunately neither of these system tables/system views are reliable, and many times they do not contain correct information. The information is corrupted mostly when you have modified your views, procedures, etc, and/or built them up in an order that did not match their dependency relation. You could use a third party tool to track the dependencies (the company I currently work for has one tool like this). These tools parse the contents of the stored procedures, views, ... and display correct dependency information. Indeed, these tools can be used to fix the information in sysdepends, ...
(in SQL Server 2008 Microsoft has introduced sys.sql_expression_dependencies, which is similar to the current third party tools, and displays the dependency information based on parsing the textual database objects, like views, triggers, etc.)
Regards,
Andras
April 10, 2008 at 3:29 am
Andras Belokosztolszki (4/10/2008)
That's useful - thanks Andras.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 10, 2008 at 3:34 am
Andras Belokosztolszki (4/10/2008)
On SQL Server 2000 you can look at the dbo.sysdepends table, on SQL Server 2005 the sys.sql_dependencies.Unfortunately neither of these system tables/system views are reliable, and many times they do not contain correct information. The information is corrupted mostly when you have modified your views, procedures, etc, and/or built them up in an order that did not match their dependency relation. You could use a third party tool to track the dependencies (the company I currently work for has one tool like this). These tools parse the contents of the stored procedures, views, ... and display correct dependency information. Indeed, these tools can be used to fix the information in sysdepends, ...
(in SQL Server 2008 Microsoft has introduced sys.sql_expression_dependencies, which is similar to the current third party tools, and displays the dependency information based on parsing the textual database objects, like views, triggers, etc.)
Regards,
Andras
I'm pretty sure that Microsoft's "View Dependencies" functionality included in Managment Studio also parses to correctly return the dependencies - plus you can drill-down through each dependecies' dependencies. Whatever it does though I can't see it uses sp_depends because I get different results when using both.
Not try to knock third-party Dependency trackers as I'm sure they provide more functionality that would make it a valuable tool (even my product has a dependency tracker that parses objects to produce accurate results) but if Microsoft's dependency viewer already does what you want it might be a good solution.
April 10, 2008 at 3:38 am
can't we achieve this by using SQL queries ? Because you are all saying sp_depends or dbo.sysdepends table will not provide the necessary or relaible informations.
Again,can't we achieve this by using SQL queries ?
Say for example,
Step 1: Find the number of FROM clause
step 2: Splitting the table names say for ex from emp,dept then first we need to identify the FROM clause after that we need to split emp and dept.
Will my logic work out ? Please suggest me.
karthik
April 10, 2008 at 4:06 am
SQLZ (4/10/2008)
...I'm pretty sure that Microsoft's "View Dependencies" functionality included in Managment Studio also parses to correctly return the dependencies - plus you can drill-down through each dependecies' dependencies. Whatever it does though I can't see it uses sp_depends because I get different results when using both.
Not try to knock third-party Dependency trackers as I'm sure they provide more functionality that would make it a valuable tool (even my product has a dependency tracker that parses objects to produce accurate results) but if Microsoft's dependency viewer already does what you want it might be a good solution.
Unfortunately SQL Server 2005 Management Studio does not parse the contents of views/stored procedures, ... other textual objects. What it does is selects everything from the sys.sql_dependencies (on 2005) plus the dependencies to CLR, XML Schema Collections, etc. On 2005 there are two kind of dependencies. One for the textual objects (where the reference is in a view, procedure, function, trigger (DDL and DML), ... . These are not precise. The other dependencies concern foreign keys, xml schema collections, CLR assemblies, defaults, rules, etc. These are correct and maintained properly by SQL Server. The dependency problem has been an issue for a long time, and 2008 will be the first version that will address it properly.
Regards,
Andras
April 10, 2008 at 4:09 am
karthikeyan (4/10/2008)
can't we achieve this by using SQL queries ? Because you are all saying sp_depends or dbo.sysdepends table will not provide the necessary or relaible informations.Again,can't we achieve this by using SQL queries ?
Say for example,
Step 1: Find the number of FROM clause
step 2: Splitting the table names say for ex from emp,dept then first we need to identify the FROM clause after that we need to split emp and dept.
Will my logic work out ? Please suggest me.
You could write a very basic parser in SQL, but it most likely be rather inaccurate and slow. It will also cost you quite a lot of time to do :). Since without a largish development effort the accuracy will be poor, you may as well just rely on sys.depends, .. Or you could look a the query SSMS is using (just use a profiler :)).
Note, that neither SQL Server Management Studio, the system views or third party tools will detect dependencies in your CLR code, in dynamic SQL or dependencies in applications 🙂
Regards,
Andras
April 10, 2008 at 4:22 am
Andras Belokosztolszki (4/10/2008)[hr
Unfortunately SQL Server 2005 Management Studio does not parse the contents of views/stored procedures, ... other textual objects. What it does is selects everything from the sys.sql_dependencies (on 2005) plus the dependencies to CLR, XML Schema Collections, etc. On 2005 there are two kind of dependencies. One for the textual objects (where the reference is in a view, procedure, function, trigger (DDL and DML), ... . These are not precise. The other dependencies concern foreign keys, xml schema collections, CLR assemblies, defaults, rules, etc. These are correct and maintained properly by SQL Server. The dependency problem has been an issue for a long time, and 2008 will be the first version that will address it properly.
Regards,
Andras
Oh well, then you might as well use sys.sql_dependencies and accept or workaround its limitations. Failing that, if you need 100% accuracy all of the time it looks like you'll need a third-party tool - or you'll need to write one yourself.
I agree with Andras though, it would be pointless to try and do this in T-SQL. It would probably be too slow and take an awful long time to get right.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply