List Stored Procedures in Database 1

[RichS]

I've looked at the threads in this forum and have not found what I'm looking for.

I would like to create a proc that returns the following information about a single stored procedure:

The column name of all the columns inputs
The column data type
The column Lenght

The resultset would look something like:

ColumnName        DataType    Length
----------------  ----------- ----------
ID                int            4
FKID              int            4
Name              varchar        30
LastUpdate        datetime       8

I've been messing around in the "sys" tables but I just don't know them very well so any help would be much appreciated.

Thanks
Rich

 

[nigelrivett]

see information_schema.parameters

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[RichS]

Thanks nigelrivett,
Armed with that information I found the following that does just what I'm looking for (

http://www.sqlteam.com/item.asp?ItemID=6948

).

DECLARE @ProcName sysname,
        @CmdName varchar(30)
SET @ProcName='pr_CR_INSERT'
SET @CmdName='cmd1'


SELECT @CmdName+'.Parameters.Append '+
       @CmdName+'.CreateParameter("'+SUBSTRING(Parameter_Name,2,128)+'",'+
       CASE Data_Type
        WHEN 'varchar' THEN 'adVarChar'
        WHEN 'char' THEN 'adVarChar'
        WHEN 'nvarchar' THEN 'adVarWChar'
        WHEN 'int' THEN 'adInteger'
        WHEN 'smallint' THEN 'adSmallInt'
        WHEN 'tinyint' THEN 'adUnsignedTinyInt'
        WHEN 'datetime' THEN 'adDate'
        ELSE 'AdVarchar'
       END+','+
       CASE WHEN Parameter_Mode='IN' THEN 'adParamInput,' 
            ELSE 'adParamOutput' END+
       CASE WHEN Character_Maximum_Length IS NULL THEN '' 
            ELSE CAST(Character_Maximum_Length AS varchar) END+
       CASE WHEN Parameter_Mode='IN' THEN ',request("'+SUBSTRING(Parameter_Name,2,128)+'")' ELSE '' END+')'
FROM Information_Schema.Parameters
WHERE Specific_Name=@ProcName