Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-15 : 08:44:06

Paresh Bhurke writes "Dear sir,

I want to retrieve all the details of 1 Stored Procedure
such as all information about its Parameters.

I'll pass Stored Procedure Name and I want to see all
the details of its parameters.

Thank you

Paresh(India)"

Günnie
Starting Member

4 Posts

Posted - 2002-02-15 : 08:58:43

Hi Paresh,
if your using MS SQL Server try: sp_help <Procedure name>

When working with MySQL i'm not sure, but I think "describe" might help.

regards
Günnie

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-15 : 10:47:01

You can do this quite easily from an ASP page;

*****************
<%
Dim objComm, param

Set objComm = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = objConn
objComm.CommandText = Request.Form("sp")
objComm.CommandType = adCmdStoredProc

objComm.Parameters.Refresh
%>
<Table Border=1>
<TR>
<TD><B>PARAMETER NAME</B></TD>
<TD><B>DATA-TYPE</B></TD>
<TD><B>DIRECTION</B></TD>
<TD><B>DATA-SIZE</B></TD>
</TR>
<% For Each param In objComm.Parameters %>
<TR>
<TD><%= param.name %></TD>
<TD><%= param.type %></TD>
<TD><%= param.direction %></TD>
<TD><%= param.size %></TD>
</TR>
<%
Next
%>
</TABLE>

******************

..this can be useful if you're stuck trying to find the right data types for a stored proc, just build a web page with a form to pass the stored proc name.

Jack

Richard101
Starting Member

30 Posts

Posted - 2002-02-16 : 16:17:11

In Enterprise Manager Right-click on the Stored Procedure, Choose 'All Tasks' / 'Generate SQL Script'.

On the General tab choose Show-all / Preview / Copy.

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-16 : 17:27:57

You can also query the text column of the syscomments table to get the body of the stored proc (or trigger or function).

SELECT text FROM syscomments WHERE id = object_id('your_proc_name')

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-17 : 23:07:01

See this link for code for doing this kind of thing via a stored procedure.

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12995[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources