CREATE VIEW Metadata.StoredProcedureParams
AS
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
p.ParameterId,
p.ParameterName,
p.TypeName AS DataType,
p.ColumnType,
p.IsOutput,
p.IsReadOnly,
p.HasDefault,
p.DefaultValue,
p.ParsedDefaultValue,
o.OBJECT_ID AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
OUTER APPLY (SELECT p.ParameterId,
p.ParameterName,
p.ColumnType,
pr.HasDefault,
pr.Value AS DefaultValue,
p.TypeName,
p.collation_name,
p.PRECISION,
pr.ParsedValue AS ParsedDefaultValue,
p.IsOutput,
p.IsReadOnly
FROM (SELECT p.parameter_id AS ParameterId,
p.NAME AS ParameterName,
ct.ColumnType,
t.NAME AS TypeName,
t.collation_name,
t.PRECISION,
p.is_output AS IsOutput,
p.is_readonly AS IsReadOnly
FROM sys.parameters p (NOLOCK)
LEFT OUTER JOIN sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id
CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.PRECISION, p.scale, db.collation_name, db.collation_name) ct
WHERE p.OBJECT_ID = o.OBJECT_ID) p
LEFT OUTER JOIN (SELECT pr.*
FROM sys.all_sql_modules m (NOLOCK)
OUTER APPLY Util.dbo.ParseSPDefaults(m.uses_quoted_identifier, m.definition) pr
WHERE m.OBJECT_ID = o.OBJECT_ID) pr ON p.ParameterId = pr.ParameterId) P
WHERE o.type = 'P'
AND NOT (s.name = 'dbo'
AND (o.name LIKE 'sys%'
OR o.name LIKE 's%diagram%'))
AND s.name NOT IN ('cdc')
AND o.is_ms_shipped = 0