Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_procedure_params_managed_view

  No additional text.


Syntax

create view sys.spt_procedure_params_managed_view
as
    select
    -- begin (for doing joins)
        object_id                   = o.object_id,
        schema_id                   = o.schema_id,
        procedure_number            = s_ap.procedure_number,
        type                        = o.type,
    -- end (for doing joins)
        PROCEDURE_CATALOG           = db_name(),
        PROCEDURE_SCHEMA            = schema_name(o.schema_id),
        PROCEDURE_NAME              = convert(nvarchar(134),o.name),
        PARAMETER_NAME              = convert(sysname,
                                                case
                                                when s_ap.parameter_id = 0 -- UDF return value
                                                then '@RETURN_VALUE'
                                                else s_ap.name
                                                end),
        ORDINAL_POSITION            = convert(smallint, s_ap.parameter_id),
        PARAMETER_TYPE              = convert(smallint,
                                                case
                                                when s_ap.parameter_id = 0 -- UDF return value
                                                then 4 -- DBPARAMTYPE_RETURNVALUE
                                                else 1+s_ap.is_output
                                                end),
        PARAMETER_HASDEFAULT        = convert(tinyint, 0),
        PARAMETER_DEFAULT           = convert(nvarchar(255),null),
        IS_NULLABLE                 = convert(bit,ColumnProperty(s_ap.object_id,s_ap.name,'AllowsNull')),
        DATA_TYPE                   = d.oledb_data_type,
        CHARACTER_MAXIMUM_LENGTH    = convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                            then coalesce(d.column_size,s_ap.real_max_length)
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                            then coalesce(d.column_size,
                                                          case
                                                          when s_ap.real_max_length = -1
                                                          then -1
                                                          else s_ap.real_max_length/2
                                                          end)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                            then coalesce(s_ap.real_max_length,u.max_length)
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH      = convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) -- DBTYPE_STR/DBTYPE_BYTES
                                            then coalesce(d.column_size,s_ap.real_max_length)
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                            then coalesce(d.column_size*2,s_ap.real_max_length)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                            then coalesce(s_ap.real_max_length,u.max_length)
                                            else null
                                            end),
        NUMERIC_PRECISION           = convert(smallint,
                                            case
                                            when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                            then s_ap.precision
                                            when (d.fixed_prec_scale =1  or d.oledb_data_type =5 or d.oledb_data_type =4)
                                            then d.data_precision
                                            else null
                                            end),
        NUMERIC_SCALE               = convert(smallint,
                                            case
                                            when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                            then s_ap.scale
                                            else null
                                            end),
        DESCRIPTION                 = convert(nvarchar(1),null),
        TYPE_NAME                   = case when s_ap.user_type_id <= 256 then d.type_name else u.name end collate database_default,
        LOCAL_TYPE_NAME             = case
                                      when s_ap.user_type_id <= 256 then d.localized_typename
                                      else u.name
                                      end collate database_default,
        XML_CATALOGNAME             = convert(sysname,
                                            case when (d.ss_dtype = 241 and xsc.name is not null) -- SQLXML, do not check oledb type, because DBTYPE_XML would be removed.
                                            then db_name()
                                            else null
                                            end),
        XML_SCHEMANAME              = convert(sysname,
                                            case when (d.ss_dtype = 241 and xsc.name is not null) -- SQLXML, do not check oledb type, because DBTYPE_XML would be removed.
                                            then schema_name(o.schema_id)
                                            else null
                                            end),
        XML_SCHEMACOLLECTIONNAME    = convert(sysname, xsc.name),
        UDT_CATALOG                 = case when s_ap.user_type_id > 256 then db_name() else null end,
        UDT_SCHEMA                  = case when s_ap.user_type_id > 256 then schema_name(u.schema_id) else null end,
        UDT_NAME                    = case when s_ap.user_type_id > 256 then u.name else null end,
        TYPE_ID                     = convert(int,case when s_ap.user_type_id > 256 then u.user_type_id else null end),
        DB_ID                       = convert(int,case when s_ap.user_type_id > 256 then db_id() else null end),
        ASSEMBLY_ID                 = convert(int,case when s_ap.user_type_id > 256 then u.assembly_id else null end),
        UDT_BOUNDCLASS              = convert(sysname,case when s_ap.user_type_id > 256 then u.assembly_class else null end) collate Latin1_General_BIN,
        ASSEMBLY_NAME               = convert(sysname,case when s_ap.user_type_id > 256 then a.name else null end),
        ASSEMBLY_VERSION            = convert(nvarchar(43),
                                                case
                                                when s_ap.user_type_id > 256
                                                then
                                                    convert(nvarchar(10),isnull(assemblyproperty(a.name, 'versionmajor'), 0)) + '.' +
                                                    convert(nvarchar(10),isnull(assemblyproperty(a.name, 'versionminor'), 0)) + '.' +
                                                    convert(nvarchar(10),isnull(assemblyproperty(a.name, 'versionbuild'), 0)) + '.' +
                                                    convert(nvarchar(10),isnull(assemblyproperty(a.name, 'versionrevision'),0))
                                                else
                                                    null
                                                end),
        ASSEMBLY_PUBLICKEY          = convert(varbinary(8000),
                                                case
                                                when s_ap.user_type_id > 256
                  then assemblyproperty(a.name, 'publickey')
                                                else null
                                                end),
        ASSEMBLY_CULTUREINFO        = convert(nvarchar(32),
                                                case
                                                when s_ap.user_type_id > 256
                                                then assemblyproperty(a.name, 'cultureinfo')
                                                else null
                                                end),
        ASSEMBLY_PERMISSIONS        = convert(int,case when s_ap.user_type_id > 256 then a.permission_set else null end),
        UDT_METADATA                = convert(varbinary(8000),
                                                case
                                                when s_ap.user_type_id > 256 then typepropertyex(u.user_type_id, 'binary_type_identifier')
                                                when s_ap.user_type_id < 128 then null
                                                else typepropertyex(t.user_type_id, 'binary_type_identifier') -- system UDTs
                                                end),
        PROG_ID                     = convert(sysname,typepropertyex(u.user_type_id,'prog_id'))

    from
        sys.all_objects o inner join
        sys.spt_parameters_numbered_parameters s_ap on
            (
                o.object_id = s_ap.object_id and
                o.type in ('P', 'FN', 'TF', 'if')
            ) inner join
        sys.types t on
            (
                s_ap.user_type_id = t.user_type_id
            ) inner join
        sys.spt_provider_types d on
            (
                d.ss_dtype = t.system_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 (t.user_type_id = u.user_type_id)
        left join
        sys.assemblies a on (a.assembly_id = u.assembly_id)

 
Last revision
See also

  sp_procedure_params_managed (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