Prev: Maintenance plan Error in Sql management studio
Next: SQL Server Agent Account
From: Abhi on 8 Jun 2010 22:59
Hi all,

can anyone help in getting below information from all stored
procedure
from one DB
we have 100 procedure in one DB of sql server 2005.instead if of
manully getting info for each procedure i want all below details in
one shot or one query


Procedure Name :
Input Parameter :
Output Parameter :
Called By :
Calls :
Dependent tables :


Thanks in advance
From: Erland Sommarskog on 9 Jun 2010 03:20
Abhi (bawejaji(a)gmail.com) writes:
> can anyone help in getting below information from all stored
> procedure
> from one DB
> we have 100 procedure in one DB of sql server 2005.instead if of
> manully getting info for each procedure i want all below details in
> one shot or one query
>
>
> Procedure Name :
> Input Parameter :
> Output Parameter :
> Called By :
> Calls :
> Dependent tables :

You cannot get that information in a single query.

You can retrieve parameter information from the catalog view
sys.parameters, see Books Online for details. However, you cannot determine
from this view whether a parameter is OUTPUT or not. That can only be deter-
mined from looking at the source code.

As for dependency to tables and procedure, the system procedure sp_depends
returns that information and you can also query the catalog view
sys.sql_dependencies directly. However, this information is rarely accurate.
Say that you do this.

1) Create a table.
2) Create a procedure referring to that table.
3) Drop and recreate the table.

The dependency information will now be missing this dependency, because it
is lost when the table is dropped.

Another problem are queries that include temp tables. Due to deferred name
resolution, SQL Server fails to record the dependecies to other table in
that query.

These issues can be worked around by building the database in a very careful
way, but without a tool for the task it is not really a viable option.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: Maintenance plan Error in Sql management studio
Next: SQL Server Agent Account