sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | List SP Column Default Value

CREATE  FUNCTION FS.VIEWPARAM(@SP nvarchar(4000))
RETURNS @SCHEMA TABLE
(   
  PARAM_NAME NVARCHAR(4000),
  PARAM_DEFVAL Nvarchar(4000)  
)
AS
BEGIN

  declare
        @w varchar(max),
        @p int, @p2 int,
        @t varchar(max)


    /* Andrey Rubanko 18 jul 2013 */

    /* fill temporary table with procedure body */

    select @w = definition
    from sys.sql_modules
    where object_id = object_id(@SP)

    declare @lines table (line varchar(500), id int identity(1, 1))

    while len(@w) > 0 begin
        set @p = charindex(char(10), @w)
        if @p > 0 begin
            insert @lines(line) values(replace(replace(SUBSTRING(@w, 1, @p - 1), char(13), ''), char(9), ' '))
            set @w = SUBSTRING(@w, @p + 1, 10000)
        end else begin
            insert @lines(line) values(replace(@w, char(13), ''))
            set @w = ''
        end
    end



    /* remove comments */

    declare 
        @i int,
        @inCommentNow bit,
        @again bit

    set @i = 1
    set @inCommentNow = 0

    while @i <= (select max(id) from @lines) begin
        select @w = line from @lines where id = @i
        set @again = 0

        if @inCommentNow = 0 begin
            set @p = patindex('%--%', @w)
            if @p > 0 begin
                set @w = SUBSTRING(@w, 1, @p - 1)

                update @lines
                set line = @w
                where id = @i

            end

            set @p = patIndex('%/*%', @w)
            if @p > 0 begin
                set @p2 = PATINDEX('%*/%', @w) 
                if @p2 > 0 begin
                    update @lines
                    set line = substring(@w, 1, @p - 1) + SUBSTRING(@w, @p2 + 2, 10000)
                    where id = @i

                    set @again = 1
                end else begin
                    set @inCommentNow = 1

                    update @lines
                    set line = SUBSTRING(@w, 1, @p - 1)
                    where id = @i
                end
            end
        end

        if @inCommentNow = 1 begin
            set @p = PATINDEX('%*/%', @w)
            if @p > 0 begin
                update @lines
                set line = SUBSTRING(@w, @p + 2, 10000)
                where id = @i

                set @inCommentNow = 0
                set @again = 1
            end else 
                update @lines
                set line = ''
                where id = @i
        end

        if @again = 0
            set @i = @i + 1
    end


    /* remove all except parameters */
    declare
        @first int,
        @last int

    set @i = 1

    while @last is null begin
        select @w = line from @lines where id = @i

        if SUBSTRING(@w, 1, 2) = 'as'
            set @last = @i - 1

        set @p = PATINDEX('% as%', @w) 
        if @last is null and @p > 0  begin
            set @w = SUBSTRING(@w, 1, @p - 1)

            update @lines
            set line = @w
            where id = @i

            if charindex('@', @w) > 0
                set @last = @i
            else 
                set @last = @i - 1
        end


        set @p = CHARINDEX('@', @w)
        if @first is null and @p > 0 begin
            set @first = @i
            set @w = SUBSTRING(@w, @p, 10000)
        end

        set @i = @i + 1
    end

    delete @lines
    where @first is null 
        or id < @first
        or id > @last



    /* decode lines to paramters */

    
    declare
        @name varchar(50),
        @type varchar(50),
        @default varchar(50)

    declare c cursor for
        select line
        from @lines
    open c
    fetch next from c into @w 
    while @@FETCH_STATUS = 0 begin
        while len(@w) > 0 begin
            set @default = null

            set @w = SUBSTRING(@w, charindex('@', @w) + 1, 10000)
            set @p = CHARINDEX(',', @w)
            --print 'start:' + @w
            if @p > 0 begin
                set @t = SUBSTRING(@w, 1, @p - 1)
                set @w = LTrim(RTrim(SUBSTRING(@w, @p + 1, 10000)))
            end else begin
                set @p = patindex('% as%', @w)
                if @p > 0 
                    set @t = SUBSTRING(@w, 1, @p - 1)
                else 
                    set @t = @w
                set @w = ''
            end

            --print 'T=' + @t
            set @p = charindex(' ', @t) 
            --if @p = 0
            --    print 'NameError:' + @t + ' ->' + cast(@p as varchar)
            set @name = SUBSTRING(@t, 1, @p - 1)
            set @t = SUBSTRING(@t, @p + 1, 10000)

            set @p = CHARINDEX('=', @t)
            if @p > 0 begin
                set @default = Replace(LTrim(RTrim(SUBSTRING(@t, @p + 1, 10000))), '''', '')
                set @t = SUBSTRING(@t, 1, @p - 1)
            end 

            set @p = CHARINDEX('(', @t)
            if @p > 0 
                set @type = LTrim(RTrim(SUBSTRING(@t, 1, @p - 1)))
            else
                set @type = LTrim(RTrim(@t))

            insert @SCHEMA (PARAM_NAME, PARAM_DEFVAL)
            values(@name, @default)
        end--while len(@w) > 0

        fetch next from c into @w 
    end
    close c
    deallocate c

   
  RETURN
END
 

GO

SELECT SP.name, VP.PARAM_NAME, rtrim(ltrim(CASE WHEN ISNULL(VP.PARAM_DEFVAL, 'null') = 'null' or VP.PARAM_DEFVAL like '%null%' then '' else replace(VP.PARAM_DEFVAL, 'output', '') end)) PARAM_DEFVAL
FROM sys.all_objects AS sp CROSS APPLY FS.VIEWPARAM(QUOTENAME(SCHEMA_NAME(SP.SCHEMA_ID)) + '.' + QUOTENAME(SP.NAME)) VP
WHERE
sp.is_ms_shipped = 0 AND SP.type = 'P'



===============================================================





SELECT
sp.object_id,
sp.name,
param.name AS [Name],
param.parameter_id AS [ID],
s1param.name AS [DataTypeSchema],
usrt.name AS [DataType],
rtrim(ltrim(CASE WHEN ISNULL(VP.PARAM_DEFVAL, 'null') = 'null' or VP.PARAM_DEFVAL like '%null%' then '' else replace(VP.PARAM_DEFVAL, 'output', '') end)) PARAM_DEFVAL,

--ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
--ISNULL(xscparam.name, N'') AS [XmlSchemaNamespace],
--ISNULL(s2param.name, N'') AS [XmlSchemaNamespaceSchema],
--ISNULL( (case param.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CASE WHEN usrt.is_table_type = 1 THEN N'structured' ELSE N'' END AS [UserType],
param.is_output AS [IsOutputParameter],
--param.is_cursor_ref AS [IsCursorParameter],
param.is_readonly AS [IsReadOnly],

db_name() AS [DatabaseName]

--CAST(
-- case 
--    when sp.is_ms_shipped = 1 then 1
--    when (
--        select 
--            major_id 
--        from 
--            sys.extended_properties 
--        where 
--            major_id = sp.object_id and 
--            minor_id = 0 and 
--            class = 1 and 
--            name = N'microsoft_database_tools_support') 
--        is not null then 1
--    else 0
--end          
--             AS bit) AS [ParentSysObj],



--SELECT SP.name, param.name, VP.PARAM_NAME, rtrim(ltrim(CASE WHEN ISNULL(VP.PARAM_DEFVAL, 'null') = 'null' or VP.PARAM_DEFVAL like '%null%' then '' else replace(VP.PARAM_DEFVAL, 'output', '') end)) PARAM_DEFVAL
FROM
sys.all_objects AS sp CROSS APPLY FS.VIEWPARAM(QUOTENAME(SCHEMA_NAME(SP.SCHEMA_ID)) + '.' + QUOTENAME(SP.NAME)) VP
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id and param.name = '@' + VP.PARAM_NAME
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
LEFT OUTER JOIN sys.schemas AS s1param ON s1param.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.xml_schema_collections AS xscparam ON xscparam.xml_collection_id = param.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2param ON s2param.schema_id = xscparam.schema_id
WHERE
sp.is_ms_shipped = 0 AND SP.type = 'P' and SP.name not like '%tbl'
--(param.name=@_msparam_0)and((sp.type = @_msparam_1 OR sp.type = @_msparam_2 OR sp.type=@_msparam_3)and(sp.name=@_msparam_4 and SCHEMA_NAME(sp.schema_id)=@_msparam_5))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'@TBL_MODIFYUSER',@_msparam_1=N'P',@_msparam_2=N'RF',@_msparam_3=N'PC',@_msparam_4=N'SP_OP_ACTIONMAIN',@_msparam_5=N'CRM'

=========================================================================


SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
SO.Name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
PM.Parameter_ID AS [ParameterID],
case 
when pm.system_type_id = pm.user_type_id then 'system_type'
else 'user_type'
end as [TypeDescr],
CASE
WHEN PM.Parameter_ID = 0 THEN 'Returns'
ELSE PM.Name
END AS [ParameterName],
'['+TYPE_NAME(PM.User_Type_ID)+']' AS [ParameterDataType],
CASE 
WHEN TYPE_NAME(PM.User_Type_ID) IN ('float', 'uniqueidentifier', 'datetime', 'bit', 'bigint', 'int', 'image', 'money', 'xml', 'varbinary', 'tinyint', 'text', 'ntext', 'smallint', 'smallmoney') THEN ''
WHEN TYPE_NAME(PM.User_Type_ID) IN ('decimal', 'numeric') THEN '(' + CAST( Precision AS VARCHAR(4) ) + ', ' + CAST( Scale AS VARCHAR(4)) + ')'
ELSE 
case 
when PM.Max_Length <> -1 then '('+CAST( PM.Max_Length AS VARCHAR(4))+')'
when (TYPE_NAME(PM.User_Type_ID) = 'xml') or (pm.system_type_id <> pm.user_type_id) then ''
else '(max)' 
end
END AS [Size],
CASE 
WHEN PM.Is_Output = 1 THEN 'Output'
ELSE 'Input'
END AS [Direction]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS PM ON SO.OBJECT_ID = PM.OBJECT_ID
INNER JOIN 
WHERE TYPE IN ('P')
ORDER BY [Schema], SO.Name, PM.parameter_id
Comments