I would like to write a query that would return both the names and types of paramters for a given stored procedure. In another words if there was a stored procedure call "MyStoredProcedure" and it took two parameters, @P1 of type int, and @P2 of nvarchar(25), then the query SELECT ?????? WHERE ????? = 'MyStoredProcedure' would return @RETURN_VALUE int, @P1 int, @P2 nvarchar(25); or at least something close to that.

I would also like to do the same thing for a table; given a table name enumerate the fields and their types.

Hi Tarang,

You can't have Input and Output parameters with teh same name. Meaning, you can't do the following:

CREATE PROCEDURE MyStoredProcedure
    @P1 int,
    @P2 nvarchar(25),
    @P1 int OUPUT,
    @P2 nvarchar(25) OUPUT
AS
   blah blah


What is your reasoning behind trying to use the same parameter names for input and output?

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

SQLScott I think you missunderstood what I wanted. Here's what I ended up with:

ALTER PROCEDURE [dbo].[spGetSPParameters]
    @sSPName nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        sysCol.NAME AS [sParameterName],
        sysTy.NAME AS [sParameterType],
        sysCol.LENGTH AS [iParameterLength]
    FROM
        sysobjects sysObj
        INNER JOIN syscolumns sysCol ON sysObj.id = sysCol.id
        INNER JOIN systypes sysTy ON sysCol.xtype = sysTy.xtype
    WHERE
        sysObj.name = @sSPName and
        sysTy.name <> 'sysname'
    ORDER BY
        sysCol.colid

END
GO

I think you need to revisit your joins posted here. I dont think it would give correct output when there are multiple entries with same name in the database. (say a column that exists with same name in multiple tables with different datatypes/length)

_________________________
- Vijay G
Strive for Perfection

Although it's true that the joins will return more than I might want I think that the WHERE clause should narrow it down to exactly what I want.