Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_sproc_columns_odbc_view

 

This is a view in the mssqlsystemresource database.




Syntax

create view sys.spt_sproc_columns_odbc_view
as
    -- (1) Generate resultset for user SProcs
    select
    -- begin (for doing joins)
        object_id                   = pro.object_id,
        schema_id                   = pro.schema_id,
        procedure_number            = pro.procedure_number,
        odbcver                     = d.odbcver,
    -- end (for doing joins)
        PROCEDURE_QUALIFIER = convert(sysname,db_name()),
        PROCEDURE_OWNER     = convert(sysname,schema_name(pro.schema_id)),
        PROCEDURE_NAME      = pro.name,
        COLUMN_NAME         = convert(sysname,
                                case
                                when s_ap.parameter_id = 0 -- UDF return value
                                then N'@RETURN_VALUE'
                                else s_ap.name
                                end),
        COLUMN_TYPE         = convert(smallint,
                                case
                                when s_ap.parameter_id = 0 -- UDF return value
                                then 5 -- SQL_RETURN_VALUE
                                else 1+s_ap.is_output
                                end),
        DATA_TYPE           = d.DATA_TYPE,
        DATA_TYPE_90        = convert(smallint, -- 9.0 compatible ODBC types
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => varbinary(max) for 9.0 clients
                                    -3
                                else
                                    d.DATA_TYPE
                                end),
        DATA_TYPE_28        = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when  d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                else
                                    d.DATA_TYPE
                                end),
        TYPE_NAME           = t.name,
        TYPE_NAME_28        = convert(sysname,
                                case
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    N'text'
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    N'ntext'
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    N'image'
                                else
                                    t.name
                                end),
        "PRECISION"         = convert(int,
                                case
                                when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => same precision as varbinary(max)
                                    0
           else
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)
                                end),
        PRECISION_28        = convert(int,
                                case
                                when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when (d.ss_dtype = 241) then -- XML
                                    1073741823
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    1073741823
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                else
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)
                                end),
        "LENGTH"            = convert(int,
                                case
                                when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)+2
                                when (d.DATA_TYPE = -151 and s_ap.max_length = 0) then -- Large UDT => same length as varbinary(max)
                                    0
                                when d.DATA_TYPE = -151 then -- Small UDT
                                    t.max_length
                                else
                                    isnull(d.length, s_ap.max_length)
                                end),
        LENGTH_90            = convert(int,
                                case
                                when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)+2
                                when d.DATA_TYPE = -151 then -- CLR UDT
                                    t.max_length
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)*2
                                else
                                    isnull(d.length, s_ap.max_length)
                                end),
        LENGTH_28           = convert(int,
                                case
                                when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)+2
                                when (d.ss_dtype = 240 and s_ap.max_length = 0) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    t.max_length
                                when (d.ss_dtype = 241) then -- XML
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
              when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)*2
                                else
                                    isnull(d.length, s_ap.max_length)
                                end),
        SCALE               = convert(smallint,
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    null
                                else
                                    OdbcScale(s_ap.system_type_id,s_ap.scale)
                                end),
        SCALE_90            = convert(smallint,
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    null
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    OdbcScale(s_ap.system_type_id,s_ap.scale)
                                end),
        RADIX               = d.RADIX,
        NULLABLE            = d.NULLABLE,
        REMARKS             = convert(varchar(254),null),   -- Remarks are NULL
        COLUMN_DEF          = convert(nvarchar(4000),NULL),
        SQL_DATA_TYPE       = d.SQL_DATA_TYPE,
        SQL_DATA_TYPE_90	= convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => varbinary(max) for 9.0 clients
                                    -3
                                else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATA_TYPE_28    = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATETIME_SUB    = d.SQL_DATETIME_SUB,
        SQL_DATETIME_SUB_90	= convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    d.SQL_DATETIME_SUB
                                end),
        CHAR_OCTET_LENGTH   = case
                                when (d.DATA_TYPE = -151 and s_ap.max_length = 0) then -- Large UDT => same length as varbinary(max)
                                  0
                                when d.DATA_TYPE = -151 then -- Small UDT
                                  t.max_length
                              else
                                  isnull(d.length, s_ap.max_length)+d.charbin
                        end,
        CHAR_OCTET_LENGTH_28 =  case
                                when (d.ss_dtype = 240 and s_ap.max_length = 0) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    t.max_length
                                when (d.ss_dtype = 241) then -- XML
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                else
                                  isnull(d.length, s_ap.max_length)+d.charbin
                                end,
        ORDINAL_POSITION    = convert(int, s_ap.parameter_id),
        IS_NULLABLE         = convert(varchar(254),rtrim(substring('NO YES',d.NULLABLE*3+1,3))),
        SS_TYPE_CATALOG_NAME = convert(sysname,
                              case
                              when (d.DATA_TYPE = -151 or d.DATA_TYPE = -153) then -- UDT or TABLE
                                  db_name()
                              else
                                  null
                              end),
        SS_TYPE_SCHEMA_NAME  = convert(sysname,
                              case
                              when (d.DATA_TYPE = -151 or d.DATA_TYPE = -153) then -- UDT or TABLE
                                  schema_name(t.schema_id)
                              else
                                  null
                              end),
        SS_UDT_CATALOG_NAME = convert(sysname,
                              case
                              when (d.DATA_TYPE = -151) then -- CLR UDT
                                  db_name()
                              else
                                  null
                              end),
        SS_UDT_SCHEMA_NAME  = convert(sysname,
                              case
                              when (d.DATA_TYPE = -151) then -- CLR UDT
                                  schema_name(t.schema_id)
                              else
                                  null
                              end),
        SS_UDT_ASSEMBLY_TYPE_NAME    = u.assembly_qualified_name,
        SS_XML_SCHEMACOLLECTION_CATALOG_NAME = convert(sysname,
                              case
                              when (d.DATA_TYPE = -152 and xsc.name is not null) then -- XML
                                  db_name()
                              else
                                  null
                              end),
        SS_XML_SCHEMACOLLECTION_SCHEMA_NAME  = convert(sysname,
                              case
                              when (d.DATA_TYPE = -152 and xsc.name is not null) then -- XML
                                  schema_name(xsc.schema_id)
                              else
                                  null
                              end),
        SS_XML_SCHEMACOLLECTION_NAME = xsc.name,
        SS_DATA_TYPE        = convert(tinyint, -- Info here is for backward compatibility - SQL 6.5
                              case
                              when (d.DATA_TYPE = -151) then -- CLR UDT
                                  23
                              else
                                  XTypeToTds(s_ap.system_type_id, t.is_nullable)
                              end)
    from
        sys.spt_procedures_user pro inner join
        sys.spt_parameters_user s_ap on
            (
                s_ap.object_id = pro.object_id and
                s_ap.procedure_number = pro.procedure_number
            ) inner join
        sys.spt_datatype_info d on
            (
                d.ss_dtype = s_ap.system_type_id and
                (
                    d.ss_usertype = s_ap.user_type_id or -- system UDTs & regular types
                    (
                        d.ss_usertype = d.ss_dtype and s_ap.user_type_id > 255 -- CLR UDTs
                    )
                ) and
                d.AUTO_INCREMENT = 0
            ) inner join
        sys.types t on
            (
                t.user_type_id   = s_ap.user_type_id
            )
        left join
        sys.assembly_types u on
            (
                u.user_type_id = s_ap.user_type_id and
                u.user_type_id = t.user_type_id
            )
        left join
        sys.xml_schema_collections xsc on
            (
                xsc.xml_collection_id = s_ap.xml_collection_id
            )

    union all

    -- (2) Generate resultset for system SProcs:
    -- Copy & pasted from (1) above and replaced with spt_procedures_system, spt_parameters_system
    select
    -- begin (for doing joins)
        object_id                   = pro.object_id,
        schema_id                   = pro.schema_id,
        procedure_number            = pro.procedure_number,
        odbcver                     = d.odbcver,
    -- end (for doing joins)
        PROCEDURE_QUALIFIER = convert(sysname,db_name()),
        PROCEDURE_OWNER     = convert(sysname,schema_name(pro.schema_id)),
        PROCEDURE_NAME      = pro.name,
        COLUMN_NAME         = convert(sysname,
                                case
                                when s_ap.parameter_id = 0 -- UDF return value
                                then N'@RETURN_VALUE'
                                else s_ap.name
                                end),
        COLUMN_TYPE         = convert(smallint,
                                case
                                when s_ap.parameter_id = 0 -- UDF return value
                                then 5 -- SQL_RETURN_VALUE
                                else 1+s_ap.is_output
                                end),
        DATA_TYPE           = d.DATA_TYPE,
        DATA_TYPE_90		= convert(smallint, -- 9.0 compatible ODBC types
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => varbinary(max) for 9.0 clients
                                    -3
                                else
                                    d.DATA_TYPE
                                end),
        DATA_TYPE_28        = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                else
                                    d.DATA_TYPE
                                end),
        TYPE_NAME           = t.name,
        TYPE_NAME_28        = convert(sysname,
                                case
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    N'text'
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    N'ntext'
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    N'image'
                                else
                                    t.name
                                end),
        "PRECISION"         = convert(int,
                                case
                                when d.DATA_TYPE in (6,7) then d.data_precision -- FLOAT/REAL
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => same precision as varbinary(max)
                                    0
                                else OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)
                                end),
        PRECISION_28        = convert(int,
                                case
                                when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when (d.ss_dtype = 241) then -- XML
                                    1073741823
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    1073741823
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                else
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)
                                end),
        "LENGTH"            = convert(int,
                                case
                                when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)+2
                                when (d.DATA_TYPE = -151 and s_ap.max_length = 0) then -- Large UDT => same length as varbinary(max)
                                    0
                                when d.DATA_TYPE = -151 then -- Small UDT
                                    t.max_length
                                else
                                    isnull(d.length, s_ap.max_length)
                                end),
        LENGTH_90            = convert(int,
                                case
                                when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)+2
                                when d.DATA_TYPE = -151 then -- CLR UDT
                                    t.max_length
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)*2
                                else
                                    isnull(d.length, s_ap.max_length)
                                end),
        LENGTH_28           = convert(int,
                                case
                                when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
                         OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)+2
                                when (d.ss_dtype = 240 and s_ap.max_length = 0) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    t.max_length
                                when (d.ss_dtype = 241) then -- XML
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when d.ss_dtype IN (40, 41, 42, 43) then --- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(s_ap.system_type_id,s_ap.max_length,s_ap.precision)*2
                                else
                                    isnull(d.length, s_ap.max_length)
                                end),
        SCALE               = convert(smallint,
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    null
                                else
                                    OdbcScale(s_ap.system_type_id,s_ap.scale)
                                end),
        SCALE_90            = convert(smallint,
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    null
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    OdbcScale(s_ap.system_type_id,s_ap.scale)
                                end),
        RADIX               = d.RADIX,
        NULLABLE            = d.NULLABLE,
        REMARKS             = convert(varchar(254),null),   -- Remarks are NULL
        COLUMN_DEF          = convert(nvarchar(4000),NULL),
        SQL_DATA_TYPE       = d.SQL_DATA_TYPE,
        SQL_DATA_TYPE_90	= convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                when (s_ap.max_length = 0 and d.ss_dtype = 240) then -- Large UDT => varbinary(max) for 9.0 clients
                                    -3
                                else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATA_TYPE_28    = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
   else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATETIME_SUB    = d.SQL_DATETIME_SUB,
        SQL_DATETIME_SUB_90	= convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    d.SQL_DATETIME_SUB
                                end),
        CHAR_OCTET_LENGTH   = case
                                when (d.DATA_TYPE = -151 and s_ap.max_length = 0) then -- Large UDT => same length as varbinary(max)
                                  0
                                when d.DATA_TYPE = -151 then -- Small UDT
                                  t.max_length
                              else
                                  isnull(d.length, s_ap.max_length)+d.charbin
                              end,
        CHAR_OCTET_LENGTH_28 =  case
                                when (d.ss_dtype = 240 and s_ap.max_length = 0) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    t.max_length
                                when (d.ss_dtype = 241) then -- XML
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (s_ap.max_length = 0 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (s_ap.max_length = 0 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                else
                                  isnull(d.length, s_ap.max_length)+d.charbin
                                end,
        ORDINAL_POSITION    = convert(int, s_ap.parameter_id),
        IS_NULLABLE         = convert(varchar(254),rtrim(substring('NO YES',d.NULLABLE*3+1,3))),
        SS_TYPE_CATALOG_NAME = convert(sysname,
                              case
                              when (d.DATA_TYPE = -151 or d.DATA_TYPE = -153) then -- UDT or TABLE
                                  db_name()
                              else
                                  null
                              end),
        SS_TYPE_SCHEMA_NAME  = convert(sysname,
                              case
                              when (d.DATA_TYPE = -151 or d.DATA_TYPE = -153) then -- UDT or TABLE
                                  schema_name(t.schema_id)
                              else
                                  null
                              end),
        SS_UDT_CATALOG_NAME = convert(sysname,
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    db_name()
                                else
                                    null
                                end),
        SS_UDT_SCHEMA_NAME  = convert(sysname,
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    schema_name(t.schema_id)
                                else
                                    null
                                end),
        SS_UDT_ASSEMBLY_TYPE_NAME    = u.assembly_qualified_name,
        SS_XML_SCHEMACOLLECTION_CATALOG_NAME = convert(sysname,
                                case
                                when (d.DATA_TYPE = -152 and xsc.name is not null) then -- XML
                                    db_name()
                                else
                                    null
                                end),
        SS_XML_SCHEMACOLLECTION_SCHEMA_NAME  = convert(sysname,
                                case
                                when (d.DATA_TYPE = -152 and xsc.name is not null) then -- XML
                                    schema_name(xsc.schema_id)
                                else
                                    null
                                end),
        SS_XML_SCHEMACOLLECTION_NAME = xsc.name,
        SS_DATA_TYPE        = convert(tinyint, -- Info here is for backward compatibility - SQL 6.5
                                case
                                when (d.DATA_TYPE = -151) then -- CLR UDT
                                    23
                                else
                                    XTypeToTds(s_ap.system_type_id, t.is_nullable)
                                end)
    from
        sys.spt_procedures_system pro inner join
        sys.spt_parameters_system s_ap on
            (
                s_ap.object_id = pro.object_id and
                s_ap.procedure_number = pro.procedure_number
            ) inner join
        sys.spt_datatype_info d on
            (
                d.ss_dtype = s_ap.system_type_id and
                (
                    d.ss_usertype = s_ap.user_type_id or -- system UDTs & regular types
                    (
                        d.ss_usertype = d.ss_dtype and s_ap.user_type_id > 255 -- CLR UDTs
                    )
                ) and
                d.AUTO_INCREMENT = 0
            ) inner join
        sys.types t on
            (
                t.user_type_id   = s_ap.user_type_id
            )
        left join
        sys.xml_schema_collections xsc on
            (
                xsc.xml_collection_id = s_ap.xml_collection_id
            )
        left join
        sys.assembly_types u on
            (
                u.user_type_id = s_ap.user_type_id
            )

 
Last revision 2008RTM
See also

  spt_sproc_columns_return_values_odbc_view (View)
sp_sproc_columns (Procedure)
sp_sproc_columns_100 (Procedure)
sp_sproc_columns_90 (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash