Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ddopen

  No additional text.


Syntax

create procedure sys.sp_ddopen;1
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774) = null,
    @p2             nvarchar(774) = null,
    @p3             nvarchar(774) = null,
    @p4             nvarchar(774) = null,
    @p5             nvarchar(774) = null,
    @p6             nvarchar(774) = null,
    @p7             int = null,
    @NameScope      int = 0,
 
    @ODBCVer        int = 2,
 
    @fUsePattern    bit = 1 -- To allow users to explicitly disable all pattern matching.
        
)
as
    set nocount on
           
    declare @ret int
         

    
    -- Yukon clients are always going to send [sys].sp_name
    
    
    if @procname = 'sp_column_privileges' or
       @procname = '[sys].sp_column_privileges'
    begin
        exec @ret = sys.sp_ddopen;2 @handle output,
            
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4
    end
    else if @procname = '[sys].sp_columns_100' or @procname = '[sys].sp_columns_90' or @procname = '[sys].sp_columns' or
           
            @procname = '[sys].sp_columns_ex_100' or @procname = '[sys].sp_columns_ex_90' or @procname = '[sys].sp_columns_ex' or
  
            @procname = 'sp_columns_90' or @procname = 'sp_columns' or
            @procname = 'sp_columns_ex_90' or @procname = 'sp_columns_ex' or
            @procname = '[sys].sp_table_type_columns_100' or
   
            @procname = 'sp_table_type_columns_100'
            
    begin
        exec @ret = sys.sp_ddopen;3 @handle output,
            
                            @procname,
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4,
                            @p5,
                            @NameScope,
                            @ODBCVer,
                            @fUsePattern
    end
    else if @procname = '[sys].sp_datatype_info_100' or @procname = '[sys].sp_datatype_info_90' or @procname = '[sys].sp_datatype_info' or
            @procname = 'sp_datatype_info_90' or @procname = 'sp_datatype_info'
    begin
        exec @ret = sys.sp_ddopen;4 @handle output,
            
                            @procname,
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p7,
                            @ODBCVer
    end
    else if @procname = 'sp_fkeys' or
            @procname = '[sys].sp_fkeys'
    begin
        exec @ret = sys.sp_ddopen;5 @handle output,
            
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4,
                            @p5,
                            @p6
    end
    else if @procname = 'sp_pkeys' or
            @procname = '[sys].sp_pkeys'
    begin
        exec @ret = sys.sp_ddopen;6 @handle output,
            
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3
    end
    else if @procname = '[sys].sp_special_columns_100' or @procname = '[sys].sp_special_columns_90' or @procname = '[sys].sp_special_columns' or
            @procname = 'sp_special_columns_90' or @procname = 'sp_special_columns'
    begin
        exec @ret = sys.sp_ddopen;7 @handle output,
            
              @procname,
     
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4,
                            @p5,
                            @p6,
                            @ODBCVer
    end
    else if @procname = '[sys].sp_sproc_columns_100' or @procname = '[sys].sp_sproc_columns_90' or @procname = '[sys].sp_sproc_columns' or
            @procname = 'sp_sproc_columns_90' or @procname = 'sp_sproc_columns'
    begin
        exec @ret = sys.sp_ddopen;8 @handle output,
            
                            @procname,
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4,
                            @ODBCVer,
                            @fUsePattern
    end
    else if @procname = '[sys].sp_statistics_100' or @procname = '[sys].sp_statistics' or
        
            @procname = 'sp_statistics'
    begin
        exec @ret = sys.sp_ddopen;9 @handle output,
            
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4,
                            @p5,
                            @p6
    end
    else if @procname = 'sp_stored_procedures' or
            @procname = '[sys].sp_stored_procedures'
           
    begin
        exec @ret = sys.sp_ddopen;10 @handle output,
           
                             @scrollopt output,
                             @ccopt output,
                             @rows output,
                             @p1,
                             @p2,
                             @p3,
                             @fUsePattern
    end
    else if @procname = 'sp_table_privileges' or
            @procname = '[sys].sp_table_privileges'
            
    begin
        exec @ret = sys.sp_ddopen;11 @handle output,
           
                             @scrollopt output,
                             @ccopt output,
                             @rows output,
                             @p1,
                             @p2,
                             @p3,
                             @fUsePattern
    end
    else if @procname = 'sp_tables' or @procname = 'sp_tables_ex' or
            @procname = '[sys].sp_tables' or @procname = '[sys].sp_tables_ex'
    begin
        exec @ret = sys.sp_ddopen;12 @handle output,
           
                            @procname,
                            @scrollopt output,
                            @ccopt output,
                            @rows output,
                            @p1,
                            @p2,
                            @p3,
                            @p4,
                            @p5,
                            @fUsePattern
    end
    else if @procname = 'sp_tableswc' or
            @procname = '[sys].sp_tableswc'
    begin
        exec @ret = sys.sp_ddopen;13 @handle output,
           
                             @procname,
                             @scrollopt output,
                             @ccopt output,
                             @rows output,
                             @p1,
                             @p2,
                             @p3,
                             @p4,
                             @fUsePattern
    end
    else
        print 'Unknown sys.sp_ddopen procedure'
    select @ret = isnull(@ret,0)
    return isnull(@ret,0)
    

create procedure sys.sp_ddopen;2
(
    @handle         int output,
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774)
)
as
    set nocount on
           
    declare @ret int
         

    create table #spcolpriv
  
    (
        TABLE_QUALIFIER sysname collate database_default null,
 
        TABLE_OWNER sysname collate database_default null,
     
        TABLE_NAME sysname collate database_default not null,
  
        COLUMN_NAME sysname collate database_default not null,
 
        GRANTOR sysname collate database_default null,
         
        GRANTEE sysname collate database_default not null,
     
        PRIVILEGE varchar(32) collate database_default not null,
        IS_GRANTABLE varchar(3) collate database_default null
  
    )

    insert into #spcolpriv exec sys.sp_column_privileges @p1,@p2,@p3,@p4

    exec @ret = sp_cursoropen @handle output,
        'select * from #spcolpriv',
        @scrollopt output, @ccopt output, @rows output
         

    drop table #spcolpriv
    
    return @ret

create procedure sys.sp_ddopen;3
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774),
    @p5             nvarchar(774),
    @NameScope      int,
     
    @ODBCVer        int,
     
    @fUsePattern    bit -- To allow users to explicitly disable all pattern matching.
            
)
as
    set nocount on
           
    declare @ret int
         

    if @procname = 'sp_columns' or @procname = '[sys].sp_columns'
    begin
        create table #spcolumns
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spcolumns exec sys.sp_columns @p1,@p2,@p3,@p4,@ODBCVer

        exec @ret = sp_cursoropen @handle output,
            'select * from #spcolumns',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spcolumns
    end
    else if @procname = 'sp_columns_90' or @procname = '[sys].sp_columns_90'
    begin
        create table #spcolumns_90
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN null,
            
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spcolumns_90 exec sys.sp_columns_90 @p1,@p2,@p3,@p4,@ODBCVer,@fUsePattern
   

        exec @ret = sp_cursoropen @handle output,
            'select * from #spcolumns_90',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spcolumns_90
    end
    else if @procname = 'sp_columns_100' or @procname = '[sys].sp_columns_100'
    begin
        create table #spcolumns_100
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_IS_SPARSE smallint null,
            SS_IS_COLUMN_SET smallint null,
            SS_IS_COMPUTED smallint null,
            SS_IS_IDENTITY smallint null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN null,
            
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spcolumns_100 exec sys.sp_columns_100 @p1,@p2,@p3,@p4,@NameScope,@ODBCVer,@fUsePattern

        exec @ret = sp_cursoropen @handle output,
            'select * from #spcolumns_100',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spcolumns_100
    end
    else if @procname = 'sp_columns_ex' or @procname = '[sys].sp_columns_ex'
    begin
        create table #spcolumns_ex
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(254) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spcolumns_ex exec sys.sp_columns_ex @p1,@p2,@p3,@p4,@p5,@ODBCVer
            

        exec @ret = sp_cursoropen @handle output,
            'select * from #spcolumns_ex',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spcolumns_ex
    end
    else if @procname = 'sp_columns_ex_90' or @procname = '[sys].sp_columns_ex_90'
    begin
        create table #spcolumns_ex_90
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(254) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN null,
            
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spcolumns_ex_90 exec sys.sp_columns_ex_90 @p1,@p2,@p3,@p4,@p5,@ODBCVer,@fUsePattern

        exec @ret = sp_cursoropen @handle output,
            'select * from #spcolumns_ex_90',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spcolumns_ex_90
    end
    else if @procname = 'sp_columns_ex_100' or @procname = '[sys].sp_columns_ex_100'
             
    begin
        create table #spcolumns_ex_100
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(254) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_IS_SPARSE smallint null,
            SS_IS_COLUMN_SET smallint null,
            SS_IS_COMPUTED smallint null,
            SS_IS_IDENTITY smallint null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN null,
            
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spcolumns_ex_100 exec sys.sp_columns_ex_100 @p1,@p2,@p3,@p4,@p5,@NameScope,@ODBCVer,@fUsePattern

        exec @ret = sp_cursoropen @handle output,
            'select * from #spcolumns_ex_100',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spcolumns_ex_100
    end
    else
    begin
        create table #sptabletypecolumns_100
        (
            TABLE_QUALIFIER sysname collate database_default null,
            TABLE_OWNER sysname collate database_default null,
 
            TABLE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            DATA_TYPE smallint not null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_IS_SPARSE smallint null,
            SS_IS_COLUMN_SET smallint null,
            SS_IS_COMPUTED smallint null,
            SS_IS_IDENTITY smallint null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN null,
            
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #sptabletypecolumns_100 exec sys.sp_table_type_columns_100 @p1,@p2,@p3,@p4,@ODBCVer,@fUsePattern

        exec @ret = sp_cursoropen @handle output,
            'select * from #sptabletypecolumns_100',
           
            @scrollopt output, @ccopt output, @rows output
     
        drop table #sptabletypecolumns_100
    end

    return @ret

create procedure sys.sp_ddopen;4
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p7             int,
     
    @ODBCVer        int
      
)
as
    set nocount on
           
    declare @ret int
         

    create table #spdatatypeinfo
    (
        TYPE_NAME           sysname  collate database_default not null,
        DATA_TYPE           smallint not null,
        PRECISION           int null,
        LITERAL_PREFIX      varchar(32) collate database_default null,
        LITERAL_SUFFIX      varchar(32)    collate database_default null,
        CREATE_PARAMS       varchar(32)    collate database_default null,
        NULLABLE            smallint   not null,
        CASE_SENSITIVE      smallint   not null,
        SEARCHABLE          smallint   not null,
        UNSIGNED_ATTRIBUTE  smallint   null,
        MONEY   smallint    not null,
        AUTO_INCREMENT      smallint    null,
        LOCAL_TYPE_NAME     sysname collate database_default null,
        MINIMUM_SCALE       smallint     null,
        MAXIMUM_SCALE       smallint   null,
        SQL_DATA_TYPE       smallint      not null,
            
        SQL_DATETIME_SUB    smallint   null,
        NUM_PREC_RADIX      int  null,
        INTERVAL_PRECISION  smallint    NULL,
        USERTYPE            smallint not null
    )

    if @procname = 'sp_datatype_info' or @procname = '[sys].sp_datatype_info'
    begin
        insert into #spdatatypeinfo exec sys.sp_datatype_info @p7,@ODBCVer
    end
    else if @procname = 'sp_datatype_info_90' or @procname = '[sys].sp_datatype_info_90'
         
    begin
        insert into #spdatatypeinfo exec sys.sp_datatype_info_90 @p7,@ODBCVer
    end
    else
    begin
        insert into #spdatatypeinfo exec sys.sp_datatype_info_100 @p7,@ODBCVer
    end

    exec @ret = sp_cursoropen @handle output,
        'select * from #spdatatypeinfo',
        @scrollopt output, @ccopt output, @rows output
         

    drop table #spdatatypeinfo
    return @ret

create procedure sys.sp_ddopen;5
(
    @handle         int output,
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774),
    @p5             nvarchar(774),
    @p6             nvarchar(774)
)
as
    set nocount on
           
    declare @ret int
         

    create table #spfkeys
    
    (
        PKTABLE_QUALIFIER sysname    collate database_default null,
        PKTABLE_OWNER sysname   collate database_default null,
 
        PKTABLE_NAME sysname  collate database_default not null,
        PKCOLUMN_NAME sysname  collate database_default not null,
        FKTABLE_QUALIFIER sysname   collate database_default null,
        FKTABLE_OWNER sysname   collate database_default null,
 
        FKTABLE_NAME sysname  collate database_default not null,
        FKCOLUMN_NAME sysname  collate database_default not null,
        KEY_SEQ smallint not null,
        UPDATE_RULE smallint null,
        DELETE_RULE smallint null,
        FK_NAME sysname collate database_default null,
         
        PK_NAME sysname collate database_default null,
         
        DEFERRABILITY smallint null
    )

    insert into #spfkeys exec sys.sp_fkeys @p1,@p2,@p3,@p4,@p5,@p6

    exec @ret = sp_cursoropen @handle output,
        'select * from #spfkeys',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #spfkeys
      
    return @ret

create procedure sys.sp_ddopen;6
(
    @handle         int output,
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774)
)
as
    set nocount on
           
    declare @ret int
         

    create table #sppkeys
    
    (
        TABLE_QUALIFIER sysname   collate database_default null,
        TABLE_OWNER sysname   collate database_default null,
   
        TABLE_NAME sysname  collate database_default not null,
 
        COLUMN_NAME sysname  collate database_default not null,
        KEY_SEQ smallint not null,
        PK_NAME sysname collate database_default null
          
    )

    insert into #sppkeys exec sys.sp_pkeys @p1,@p2,@p3
         

    exec @ret = sp_cursoropen @handle output,
        'select * from #sppkeys',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #sppkeys
      
    return @ret

create procedure sys.sp_ddopen;7
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774),
    @p5             nvarchar(774),
    @p6             nvarchar(774),
    @ODBCVer        int
      
)
as
    set nocount on
           
    declare @ret int
         

    create table #spspeccol
  
    (
        SCOPE smallint null,
 
        COLUMN_NAME sysname collate database_default not null,
 
        DATA_TYPE smallint not null,
        TYPE_NAME sysname collate database_default not null,
   
        PRECISION int null,
  
        LENGTH int null,
     
        SCALE smallint null,
 
        PSEUDO_COLUMN smallint null
    )

    -- VSTS 85838: Using the correct stored procedure name here. The bug was introduced by CTS# 927686
    -- that was a result of a copy-paste mistake. I have verified that the original change was not intentional
    if @procname = 'sp_special_columns' or @procname = '[sys].sp_special_columns'
    begin
        insert into #spspeccol exec sys.sp_special_columns @p1,@p2,@p3,@p4,@p5,@p6,@ODBCVer
      
    end
    else if @procname = 'sp_special_columns_90' or @procname = '[sys].sp_special_columns_90'
     
    begin
        insert into #spspeccol exec sys.sp_special_columns_90 @p1,@p2,@p3,@p4,@p5,@p6,@ODBCVer
   
    end
    else
    if @procname = 'sp_special_columns_90' or @procname = '[sys].sp_special_columns_90'
          
    begin
        insert into #spspeccol exec sys.sp_special_columns_100 @p1,@p2,@p3,@p4,@p5,@p6,@ODBCVer
  
    end
    else
    begin
        insert into #spspeccol exec sys.sp_special_columns_100 @p1,@p2,@p3,@p4,@p5,@p6,@ODBCVer
  
    end

    exec @ret = sp_cursoropen @handle output,
        'select * from #spspeccol',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #spspeccol
    
    return @ret

create procedure sys.sp_ddopen;8
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774),
    @ODBCVer        int,
     
    @fUsePattern    bit -- To allow users to explicitly disable all pattern matching.
            
)
as
    set nocount on
           
    declare @ret int
         

    if @procname = 'sp_sproc_columns' or @procname = '[sys].sp_sproc_columns'
    begin
        create table #spproccol
        (
            PROCEDURE_QUALIFIER sysname collate database_default null,
            PROCEDURE_OWNER sysname collate database_default null,
            PROCEDURE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            COLUMN_TYPE smallint not null,
            DATA_TYPE smallint null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spproccol exec sys.sp_sproc_columns @p1,@p2,@p3,@p4,@ODBCVer

        exec @ret = sp_cursoropen @handle output,
            'select * from #spproccol',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spproccol
    end
    else if @procname = 'sp_sproc_columns_90' or @procname = '[sys].sp_sproc_columns_90'
         
    begin
        create table #spproccol_90
        (
            PROCEDURE_QUALIFIER sysname collate database_default null,
            PROCEDURE_OWNER sysname collate database_default null,
            PROCEDURE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            COLUMN_TYPE smallint not null,
            DATA_TYPE smallint null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate database_default null,
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spproccol_90 exec sys.sp_sproc_columns_90 @p1,@p2,@p3,@p4,@ODBCVer,@fUsePattern

        exec @ret = sp_cursoropen @handle output,
            'select * from #spproccol_90',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spproccol_90
    end
    else
    begin
        create table #spproccol_100
        (
            PROCEDURE_QUALIFIER sysname collate database_default null,
            PROCEDURE_OWNER sysname collate database_default null,
            PROCEDURE_NAME sysname collate database_default not null,
            COLUMN_NAME sysname collate database_default not null,
            COLUMN_TYPE smallint not null,
            DATA_TYPE smallint null,
            TYPE_NAME sysname collate database_default not null,
            PRECISION int null,
            LENGTH int null,
 
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) collate database_default null,
            COLUMN_DEF nvarchar(4000) collate database_default null,
            SQL_DATA_TYPE smallint null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) collate database_default null,
            SS_TYPE_CATALOG_NAME sysname collate database_default null,
            SS_TYPE_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_CATALOG_NAME sysname collate database_default null,
            SS_UDT_SCHEMA_NAME sysname collate database_default null,
            SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate database_default null,
            SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default null,
          
            SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default null,
           
            SS_XML_SCHEMACOLLECTION_NAME sysname collate database_default null,
            SS_DATA_TYPE tinyint null
        )

        insert into #spproccol_100 exec sys.sp_sproc_columns_100 @p1,@p2,@p3,@p4,@ODBCVer,@fUsePattern

        exec @ret = sp_cursoropen @handle output,
            'select * from #spproccol_100',
            @scrollopt output, @ccopt output, @rows output
     
        drop table #spproccol_100
    end

    return @ret

create procedure sys.sp_ddopen;9
(
    @handle         int output,
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774),
    @p5             nvarchar(774),
    @p6             nvarchar(774)
)
as
    set nocount on
           
    declare @ret int
         

    create table #spstatistics
    (
        TABLE_QUALIFIER sysname   collate database_default null,
        TABLE_OWNER sysname   collate database_default null,
   
        TABLE_NAME sysname  collate database_default not null,
 
        NON_UNIQUE smallint null,
        INDEX_QUALIFIER sysname collate database_default null,
 
        INDEX_NAME sysname collate database_default null,
      
        TYPE smallint not null,
        SEQ_IN_INDEX smallint null,
        COLUMN_NAME sysname collate database_default null,
     
        COLLATION char(1) collate database_default null,
       
        CARDINALITY int null,
        PAGES int null,
      
        FILTER_CONDITION varchar(128) collate database_default null
    )

    insert into #spstatistics exec sys.sp_statistics @p1,@p2,@p3,@p4,@p5,@p6

    exec @ret = sp_cursoropen @handle output,
        'select * from #spstatistics',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #spstatistics
 
    return @ret

create procedure sys.sp_ddopen;10
(
    @handle         int output,
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @fUsePattern    bit -- To allow users to explicitly disable all pattern matching.
            
)
as
    set nocount on
           
    declare @ret int
         

    create table #spprocedures
    (
        PROCEDURE_QUALIFIER sysname  collate database_default null,
        PROCEDURE_OWNER sysname  collate database_default null,
        PROCEDURE_NAME nvarchar(134) collate database_default not null, --134=sysname+';'+ltrim(str(c.number,5))
        NUM_INPUT_PARAMS int null,
        NUM_OUTPUT_PARAMS int null,
        NUM_RESULT_SETS int null,
        REMARKS varchar(254) collate database_default null,
    
        PROCEDURE_TYPE smallint null
    )

    insert into #spprocedures exec sys.sp_stored_procedures @p1,@p2,@p3,@fUsePattern
             

    exec @ret = sp_cursoropen @handle output,
        'select * from #spprocedures',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #spprocedures
 
    return @ret

create procedure sys.sp_ddopen;11
(
    @handle         int output,
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @fUsePattern    bit -- To allow users to explicitly disable all pattern matching.
            
)
as
    set nocount on
           
    declare @ret int
         

    create table #sptabpriv
  
    (
        TABLE_QUALIFIER sysname collate database_default null,
 
        TABLE_OWNER sysname collate database_default null,
     
        TABLE_NAME sysname collate database_default not null,
  
        GRANTOR sysname collate database_default null,
         
        GRANTEE sysname collate database_default not null,
     
        PRIVILEGE varchar(32) collate database_default not null,
        IS_GRANTABLE varchar(3) collate database_default null
  
    )

    insert into #sptabpriv exec sys.sp_table_privileges @p1,@p2,@p3,@fUsePattern

    exec @ret = sp_cursoropen @handle output,
        'select * from #sptabpriv',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #sptabpriv
    
    return @ret

create procedure sys.sp_ddopen;12
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),
    @p2             nvarchar(774),
    @p3             nvarchar(774),
    @p4             nvarchar(774),
    @p5             nvarchar(774),
    @fUsePattern    bit -- To allow users to explicitly disable all pattern matching.
            
)
as
    set nocount on
           
    declare @ret int
         

    create table #sptables
   
    (
        TABLE_QUALIFIER sysname collate database_default null,
 
        TABLE_OWNER sysname collate database_default null,
     
        TABLE_NAME sysname collate database_default null,
      
        TABLE_TYPE  varchar(32) collate database_default null,
 
        REMARKS varchar(254) collate database_default null
     
    )

    if @procname = 'sp_tables' or @procname = '[sys].sp_tables'
    begin
        insert into #sptables exec sys.sp_tables @p1,@p2,@p3,@p4,@fUsePattern
    end
    else
    begin
        insert into #sptables exec sys.sp_tables_ex @p1,@p2,@p3,@p4,@p5,@fUsePattern
             
    end

    exec @ret = sp_cursoropen @handle output,
        'select * from #sptables',
        @scrollopt output, @ccopt output, @rows output
         
    drop table #sptables
     
    return @ret

create procedure sys.sp_ddopen;13
(
    @handle         int output,
    @procname       sysname,
 
    @scrollopt      int output,
    @ccopt          int output,
    @rows           int output,
    @p1             nvarchar(774),  -- @table_name
             
    @p2             nvarchar(774),  -- @table_owner
            
    @p3             nvarchar(774),  -- @table_qualifier
        
    @p4             nvarchar(774),  -- @table_type
             
    @fUsePattern    bit -- To allow users to explicitly disable all pattern matching.
            
)
as
    set nocount on
           
    declare @ret            int

    create table #sptableswc
 
    (
        TABLE_QUALIFIER sysname collate database_default null,
 
        TABLE_OWNER sysname collate database_default null,
     
        TABLE_NAME sysname collate database_default null,
      
        TABLE_TYPE varchar(254) collate database_default null,
 
        REMARKS varchar(254) collate database_default null
     
    )

    exec sys.sp_tableswc @p1, @p2, @p3, @p4, @fUsePattern, 1
   

    exec @ret = sp_cursoropen @handle output,
        'select * from #sptableswc',
        @scrollopt output, @ccopt output, @rows output
         

    drop table #sptableswc
   

    return @ret

 
Last revision 2008RTM
See also

  spt_all_procedures (View)
spt_procedures_user (View)
sp_stored_procedures (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