Sql to get column name with datatype and precision together

Question

Hi all I have a written a stored procedure which displays parameter values and data types as follows

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
P.name AS [Parameter],
TYPE_NAME(P.user_type_id) AS [DataType]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P 
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN')) AND SO.name = 'User_Update'
ORDER BY [Schema], SO.name, P.parameter_id

But I would like to query it to display as follows along with whether it is a mandatory or not can some one help me

enter image description here

possible duplicate of [Determine whether SP Parameter has a Default Value in T-SQL](http://stackoverflow.com/questions/14652361/determine-whether-sp-parameter-has-a-default-value-in-t-sql) — Martin Smith, Dec 27 '14 at 23:56
The 2 questions are not the same but your link contains lots of useful information and what he needs — Julien Vavasseur, Aug 07 '15 at 13:15

score 1 · Answer 1 · answered Aug 07 '15 at 13:13

You can start with this query:

select top 10 SCHEMA_NAME(o.schema_id) AS [Schema], o.name AS [Function]
, p.parameter_id, p.name, t.name, t.max_length, t.precision, t.scale 
from sys.objects as o
    inner join sys.parameters as p on p.object_id = o.object_id
    inner join sys.types as t on t.system_type_id = p.system_type_id
where o.type in ('P','FN') AND o.name = 'User_Update'
    --  and p.name <> '' / p.parameter_id >= 1 remove output parameter
order by o.name, p.parameter_id

The info you need where in sys.parameters.

Tips:

For char or varchar you can look at max_length.
For numeric, you can look at precision and scale
Parameter 0 is the output for a scalar function

Sql to get column name with datatype and precision together

1 Answers1