January 21, 2009 at 3:00 pm
I am trying to write a stored procedure to return metadata about a given stored procedure.
I have found the following code on this site:
SELECT Parameter_Name AS ParameterName
, Ordinal_Position AS ParameterPosition
, Data_Type AS DataType
, Character_Maximum_Length AS Length
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE Specific_Name = @StoredProcedureName
(http://www.sqlservercentral.com/scripts/Miscellaneous/30744/)
However, the above code only returns the length of a string, not the length of a number/date.
Is there a way to return the size of every data type as well?
January 21, 2009 at 3:43 pm
This might help.
select
p.name,
t.name,
p.max_length
from sys.parameters p
inner join sys.types t on p.system_type_id = p.system_type_id
where object_id = object_id('your-procedure')
.
January 21, 2009 at 6:01 pm
That works great. Thanks, Jacob.
I also realized that another option would be to use the built-in stored procedure sp_sproc_columns.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply