USE master
GO
IF OBJECT_ID('sp_ParamDefault') IS NULL EXEC ('CREATE PROCEDURE sp_ParamDefault AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_ParamDefault
@ProcName SYSNAME = NULL
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ObjectId INT = OBJECT_ID(LTRIM(RTRIM(@ProcName)))
IF @ObjectId IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_ParamDefault',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
SELECT p.ParameterId,
p.ParameterName,
p.TypeName AS DataType,
p.ColumnType,
p.IsOutput,
p.IsReadOnly,
p.HasDefault,
p.DefaultValue,
p.ParsedDefaultValue
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 o.OBJECT_ID = @ObjectId
GO
EXEC sys.sp_ms_marksystemobject sp_ParamDefault
GO