DROP TABLE #ParseSPDefaults
go
SELECT s.name AS procedureName,
k.*
INTO #ParseSPDefaults
FROM sys.procedures AS s (NOLOCK)
INNER JOIN sys.all_sql_modules AS m ON m.OBJECT_ID = s.OBJECT_ID
CROSS APPLY (SELECT CASE WHEN p.ParameterId IS NULL THEN 'MISS_SYS'
WHEN pd.ParameterId IS NULL THEN 'CLR'
END AS MissType,
ISNULL(p.ParameterId, pd.ParameterId) AS ParameterId,
p.ParameterName AS SYSNAME,
pd.ParameterName AS CLRName,
TypeName,
ColumnType,
HasDefault,
Value,
ParsedValue
FROM (SELECT p.parameter_id AS ParameterId,
p.name AS ParameterName,
y.name AS TypeName,
ct.ColumnType
FROM sys.parameters AS p (NOLOCK)
INNER JOIN sys.types AS y (NOLOCK) ON y.user_type_id = p.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, p.max_length, p.PRECISION, p.scale, db.collation_name, db.collation_name) ct
WHERE p.OBJECT_ID = s.OBJECT_ID) AS p
FULL OUTER JOIN (SELECT ParameterId,
ParameterName,
HasDefault,
Value,
ParsedValue
FROM Util.dbo.ParseSPDefaults(m.definition) AS pd) AS pd ON pd.ParameterID = p.ParameterID) AS k ;
GO
SELECT *
FROM #ParseSPDefaults
WHERE misstype IS NOT NULL