Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/i386/instcat.sql

Text File | 1995-12-05 | 129.0 KB | 4,569 lines

/*
**    INSTCAT.SQL
**    Installs catalog stored procedures on the Microsoft SQL Server.
**    Copyright 1992-1995, Microsoft Corp.    All rights reserved.
*/

/*
NOTE:  you MUST change the last row inserted into spt_server_info
to be version number of this file.    the convention is j.nn.bbb, where
jj is the major version number ('6' now), nn is the minor version number
('50' now), and bbb is the build number.
*/

/****************************************************************************/
/* This portion sets up the ability to perform all the functions in this    */
/* script                                                                    */
/****************************************************************************/
use master
go
dump tran master with no_log
go

set quoted_identifier on
go

if exists (select * from sysobjects
       where name = 'sp_configure' and sysstat & 0xf = 4)
begin
    execute sp_configure 'update',1
end
reconfigure with override
go

/*
** If old versions of tables exist, drop them.
*/
if (exists (select * from sysobjects
        where name = 'MSdatatype_info' and sysstat & 0xf = 3))
    drop table MSdatatype_info
go
if (exists (select * from sysobjects
        where name = 'MSdatatype_info_ext' and sysstat & 0xf = 3))
    drop table MSdatatype_info_ext
go
if (exists (select * from sysobjects
        where name = 'MStable_types' and sysstat & 0xf = 3))
    drop table MStable_types
go
if (exists (select * from sysobjects
        where name = 'MSserver_info' and sysstat & 0xf = 3))
    drop table MSserver_info
go
if (exists (select * from sysobjects
        where name = 'spt_table_types' and sysstat & 0xf = 3))
    drop table spt_table_types    /* no longer used */
go

/*
** If tables or procs already exist, drop them.
*/

if (exists (select * from sysobjects
        where name = 'spt_datatype_info' and sysstat & 0xf = 3))
    drop table spt_datatype_info
go
if (exists (select * from sysobjects
        where name = 'spt_datatype_info_ext' and sysstat & 0xf = 3))
    drop table spt_datatype_info_ext
go
if (exists (select * from sysobjects
        where name = 'spt_server_info' and sysstat & 0xf = 3))
    drop table spt_server_info
go
if (exists (select * from sysobjects
        where name = 'sp_tables' and sysstat & 0xf = 4))
    drop proc sp_tables
go
if (exists (select * from sysobjects
        where name = 'sp_statistics' and sysstat & 0xf = 4))
    drop proc sp_statistics
go
if (exists (select * from sysobjects
        where name = 'sp_columns' and sysstat & 0xf = 4))
    drop proc sp_columns
go
if (exists (select * from sysobjects
        where name = 'sp_fkeys' and sysstat & 0xf = 4))
    drop proc sp_fkeys
go
if (exists (select * from sysobjects
        where name = 'sp_pkeys' and sysstat & 0xf = 4))
    drop proc sp_pkeys
dump tran master with no_log
go

go
if (exists (select * from sysobjects
        where name = 'sp_stored_procedures' and sysstat & 0xf = 4))
    drop proc sp_stored_procedures
go
if (exists (select * from sysobjects
        where name = 'sp_sproc_columns' and sysstat & 0xf = 4))
    drop proc sp_sproc_columns
go
if (exists (select * from sysobjects
        where name = 'sp_table_privileges' and sysstat & 0xf = 4))
    drop proc sp_table_privileges
go
if (exists (select * from sysobjects
        where name = 'sp_column_privileges' and sysstat & 0xf = 4))
    drop proc sp_column_privileges
go
if (exists (select * from sysobjects
        where name = 'sp_server_info' and sysstat & 0xf = 4))
    drop proc sp_server_info
go
if (exists (select * from sysobjects
        where name = 'sp_datatype_info' and sysstat & 0xf = 4))
    drop proc sp_datatype_info
go
if (exists (select * from sysobjects
        where name = 'sp_special_columns' and sysstat & 0xf = 4))
    drop proc sp_special_columns
go
if (exists (select * from sysobjects
        where name = 'sp_databases' and sysstat & 0xf = 4))
    drop proc sp_databases
go
if (exists (select * from sysobjects
        where name = 'sp_ddopen' and sysstat & 0xf = 4))
    drop proc sp_ddopen
go

dump tran master with no_log
go

print 'creating table spt_datatype_info_ext'
go
create table spt_datatype_info_ext (
                user_type        smallint    not null,
                CREATE_PARAMS    varchar(32) null,
                AUTO_INCREMENT    smallint null)
go

create unique clustered index datatypeinfoextclust on spt_datatype_info_ext(user_type,AUTO_INCREMENT)
go

grant select on spt_datatype_info_ext to public
go


insert into spt_datatype_info_ext
    /* CHAR      user_type, create_params, auto_increment */
    values             (1,    'length' ,0)

insert into spt_datatype_info_ext
    /* VARCHAR     user_type, create_params, auto_increment */
    values             (2,    'max length' ,0)

insert into spt_datatype_info_ext
    /* BINARY     user_type, create_params, auto_increment */
    values             (3,    'length' ,0)

insert into spt_datatype_info_ext
    /* VARBINARY user_type, create_params, auto_increment */
    values             (4,    'max length' ,0)

if    (charindex('6.00', @@version) > 0 or
     charindex('6.50', @@version) > 0)
begin    /*    Add 6.0 data types */
    insert into spt_datatype_info_ext
        /* DECIMAL user_type, create_params, auto_increment */
        values             (26,    'precision,scale' ,0)

    insert into spt_datatype_info_ext
        /* NUMERIC user_type, create_params, auto_increment */
        values             (25,    'precision,scale' ,0)

    insert into spt_datatype_info_ext
        /* DECIMAL IDENTITY user_type, create_params, auto_increment */
        values             (26,    'precision' ,1)

    insert into spt_datatype_info_ext
        /* NUMERIC IDENTITY user_type, create_params, auto_increment */
        values             (25,    'precision' ,1)

end
else    /*    Pre 6.0 server, add SYSNAME create param */
    begin
        insert into spt_datatype_info_ext
            /* SYSNAME     user_type, create_param, auto_increments */
            values             (18,    'max length' ,0)

    end
go

print 'creating table spt_datatype_info'
go
create table spt_datatype_info (
    ss_dtype            tinyint     not null,
    fixlen                int         null,        /* datatype len for variable, else null */
    ODBCVer             tinyint     null,        /* version if needed, else null */
    TYPE_NAME            varchar(30)    not null,
    DATA_TYPE            smallint    not null,
    data_precision        int         null,
    numeric_scale        smallint    null,    /* min scale if 6.0 */
    RADIX                smallint    null,
    length                int         null,
    LITERAL_PREFIX        varchar(32) null,
    LITERAL_SUFFIX        varchar(32) null,
    CREATE_PARAMS        varchar(32) 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     varchar(128)null,
    charbin             tinyint     null, /* 0 for char/binary types, NULL for all others */
    SQL_DATA_TYPE        smallint    not null,
    SQL_DATETIME_SUB    smallint    null)
go

create unique clustered index datatypeinfoclust on spt_datatype_info(ss_dtype,fixlen,ODBCVer,AUTO_INCREMENT)
go

grant select on spt_datatype_info to public
go

/* Get case sensitivity */
if 'A' = 'A' /* create dummy begin block */
begin
    declare @case smallint

    begin tran
    select @case = 0
    select @case = 1 where 'a' <> 'A'

    /* Local Binary */
    insert into spt_datatype_info values
    (45,null,null,'binary',-2,null,null,null,null,'0x',null,'length',1,0,2,null,0,null,'binary',0,-2,null)

    /* Local Bit */
    insert into spt_datatype_info values
    (50,null,null,'bit',-7,1,0,2,null,null,null,null,0,0,2,null,0,null,'bit',null,-7,null)

    /* Local Char */
    insert into spt_datatype_info values
    (47,null,null,'char',1,null,null,null,null,'''','''','length',1,@case,3,null,0,null,'char',0,1,null)

    /* Local Datetime */
    insert into spt_datatype_info values
    (61,8,2,'datetime',11,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
    insert into spt_datatype_info values
    (61,8,3,'datetime',93,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)

    /* Local Smalldatetime */
    insert into spt_datatype_info values
    (58,4,2,'smalldatetime',11,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
    insert into spt_datatype_info values
    (58,4,3,'smalldatetime',93,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)

    /* Local Datetimn */
    insert into spt_datatype_info values
    (111,4,2,'smalldatetime',11,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
    insert into spt_datatype_info values
    (111,4,3,'smalldatetime',93,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
    insert into spt_datatype_info values /* sql server type is 'datetimn' */
    (111,8,2,'datetime',11,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
    insert into spt_datatype_info values
    (111,8,3,'datetime',93,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)

    /* Local Float */
    insert into spt_datatype_info values
    (62,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
    insert into spt_datatype_info values
    (62,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)

    /* Local Floatn */
    insert into spt_datatype_info values /* sql server type is 'floatn' */
    (109,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
    insert into spt_datatype_info values
    (109,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
    insert into spt_datatype_info values /* sql server type is 'floatn' */
    (109,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
    insert into spt_datatype_info values
    (109,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)

    /* Local Real */
    insert into spt_datatype_info values
    (59,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
    insert into spt_datatype_info values
    (59,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)

    /* Local Smallmoney */
    insert into spt_datatype_info values
    (122,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)

    /* Local Money */
    insert into spt_datatype_info values
    (60,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)

    /* Local Moneyn */
    insert into spt_datatype_info values    /* sql server type is 'moneyn' */
    (110,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)
    insert into spt_datatype_info values    /* sql server type is 'moneyn' */
    (110,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)

    /* Local Int */
    insert into spt_datatype_info values
    (56,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)

    /* Local Intn */
    insert into spt_datatype_info values /* sql server type is 'intn' */
    (38,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)
    insert into spt_datatype_info values /* sql server type is 'intn' */
    (38,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
    insert into spt_datatype_info values
    (38,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)

    /* Local Smallint */
    insert into spt_datatype_info values
    (52,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
    insert into spt_datatype_info values
    (52,2,1,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)

    /* Local Tinyint */
    insert into spt_datatype_info values
    (48,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)

    /* Local Text */
    insert into spt_datatype_info values
    (35,null,null,'text',-1,2147483647,null,null,2147483647,'''','''',null,1,@case,1,null,0,null,'text',0,-1,null)

    /* Local Varbinary */
    insert into spt_datatype_info values
    (37,null,null,'varbinary',-3,null,null,null,null,'0x',null,'max length',1,0,2,null,0,null,'varbinary',0,-3,null)

    /* Local Varchar */
    insert into spt_datatype_info values
    (39,null,null,'varchar',12,null,null,null,null,'''','''','max length',1,@case,3,null,0,null,'varchar',0,12,null)

    /* Local Image */
    insert into spt_datatype_info values
    (34,null,null,'image',-4,2147483647,null,null,2147483647,'0x',null,null,1,0,0,null,0,null,'image',0,-4,null)

    if    (charindex('6.00', @@version) > 0 or
         charindex('6.50', @@version) > 0)
    begin    /*    Add 6.0 data types */
        /* Local Decimaln */
        insert into spt_datatype_info values    /* sql server type is 'decimaln' */
        (106,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)

        /* Local Numericn */
        insert into spt_datatype_info values    /* sql server type is 'numericn' */
        (108,null,null,'numeric',2,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)

        /* Local Decimal */
        insert into spt_datatype_info values    /* sql server type is 'decimaln' */
        (55,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)

        /* Local Numeric */
        insert into spt_datatype_info values    /* sql server type is 'numericn' */
        (63,null,null,'numeric',2    ,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)

        /* Identity attribute data types */

        /* Identity Int */
        insert into spt_datatype_info values
        (56,null,null,'int identity',4,10,0,10,null,null,null,null,0,0,2,0,0,1,'int identity',null,4,null)

        /* Identity Smallint */
        insert into spt_datatype_info values
        (52,null,null,'smallint identity',5,5,0,10,null,null,null,null,0,0,2,0,0,1,'smallint identity',null,5,null)

        /* Identity Tinyint */
        insert into spt_datatype_info values
        (48,null,null,'tinyint identity',-6,3,0,10,null,null,null,null,0,0,2,1,0,1,'tinyint identity',null,-6,null)

        /* Identity Numeric */
        insert into spt_datatype_info values    /* sql server type is 'decmaln' */
        (106,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)
        insert into spt_datatype_info values    /* sql server type is 'decmaln' */
        (55,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)

        /* Identity Numeric */
        insert into spt_datatype_info values    /* sql server type is 'decmaln' */
        (108,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)
        insert into spt_datatype_info values    /* sql server type is 'decmaln' */
        (63,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)

    end
    commit tran
end
go

dump tran master with no_log
go

print 'creating table spt_server_info'
go
create table spt_server_info (
              attribute_id        int NOT NULL,
              attribute_name    varchar(60) NOT NULL,
              attribute_value    varchar(255) NOT NULL)
go

create unique clustered index serverinfoclust on spt_server_info(attribute_id)
go

insert into spt_server_info
    values (1,'DBMS_NAME','Microsoft SQL Server')
insert into spt_server_info
    values (2,'DBMS_VER',@@version)
insert into spt_server_info
    values (10,'OWNER_TERM','owner')
insert into spt_server_info
    values (11,'TABLE_TERM','table')
insert into spt_server_info
    values (12,'MAX_OWNER_NAME_LENGTH','30')
insert into spt_server_info
    values (13,'TABLE_LENGTH','30')
insert into spt_server_info
    values (14,'MAX_QUAL_LENGTH','30')
insert into spt_server_info
    values (15,'COLUMN_LENGTH','30')
if 'A' = 'a' /* If not case sensitive server */
begin
    insert into spt_server_info
        values (16,'IDENTIFIER_CASE','MIXED')
end
else
begin
    insert into spt_server_info
        values (16,'IDENTIFIER_CASE','SENSITIVE')
end
insert into spt_server_info
    values (17,'TX_ISOLATION','2')
if    (charindex('6.00', @@version) > 0 or
     charindex('6.50', @@version) > 0)
begin    /*    Add 6.0 collation sequence */
    insert into spt_server_info
        select 18,'COLLATION_SEQ',
            'charset='+t2.name+' sort_order='+t1.name
            +' charset_num='+rtrim(convert(char(4),t1.csid))+
            ' sort_order_num='+rtrim(convert(char(4),t1.id))
        from syscharsets t1, syscharsets t2, sysconfigures t3
        where t1.csid=t2.id and t1.id=t3.value and t3.config=1123
end
else
begin    /*    Add 4.2x collation sequence */
    insert into spt_server_info
        select 18,'COLLATION_SEQ',
            'charset='+t2.name+' sort_order='+t1.name
            +' charset_num='+rtrim(convert(char(4),t1.csid))+
            ' sort_order_num='+rtrim(convert(char(4),t1.id))
        from syscharsets t1, syscharsets t2, sysconfigures t3
        where t1.csid=t2.id and t1.id=t3.value and t3.config=123
end
insert into spt_server_info
    values (19,'SAVEPOINT_SUPPORT','Y')
insert into spt_server_info
    values (20,'MULTI_RESULT_SETS','Y')
insert into spt_server_info
    values (22,'ACCESSIBLE_TABLES','Y')
insert into spt_server_info
    values (100,'USERID_LENGTH','30')
insert into spt_server_info
    values (101,'QUALIFIER_TERM','database')
insert into spt_server_info
    values (102,'NAMED_TRANSACTIONS','Y')
insert into spt_server_info
    values (103,'SPROC_AS_LANGUAGE','Y')
insert into spt_server_info
    values (104,'ACCESSIBLE_SPROC','Y')
insert into spt_server_info
    values (105,'MAX_INDEX_COLS','16')
insert into spt_server_info
    values (106,'RENAME_TABLE','Y')
insert into spt_server_info
    values (107,'RENAME_COLUMN','Y')
insert into spt_server_info
    values (108,'DROP_COLUMN','N')
insert into spt_server_info
    values (109,'INCREASE_COLUMN_LENGTH','N')
if    (charindex('6.50', @@version) = 0)
begin
    insert into spt_server_info
        values (110,'DDL_IN_TRANSACTION','N')
end
else
begin
    insert into spt_server_info
        values (110,'DDL_IN_TRANSACTION','Y')
end
insert into spt_server_info
    values (111,'DESCENDING_INDEXES','N')
insert into spt_server_info
    values (112,'SP_RENAME','Y')
insert into spt_server_info
    values (113,'REMOTE_SPROC','Y')
insert into spt_server_info
    values (500,'SYS_SPROC_VERSION','6.50.156')
go

grant select on spt_server_info to public
go

print 'creating sp_column_privileges'
go

/*    Procedure for pre 6.50 server */
CREATE PROCEDURE sp_column_privileges (
            @table_name         varchar(32),
            @table_owner        varchar(32) = null,
            @table_qualifier    varchar(32) = null,
            @column_name        varchar(90) = null)
as

    declare @table_id     int
    DECLARE @full_table_name    char(70)
    declare @low smallint                     /* range of userids to check */
    declare @high smallint
    declare @owner_uid smallint

    select @low = 0, @high = 32767

    if @column_name is null /*    If column name not supplied, match all */
        select @column_name = '%'

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end
    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = @table_name
    end
    else
    begin    /* Qualified table name */
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    /*    Get Object ID */
    select @table_id = object_id(@full_table_name)

    if (@@trancount <> 0)
    begin    /* If inside a transaction */
        raiserror 20003 'The procedure ''sp_column_privileges'' cannot be executed from within a transaction.'
        return
    end

    /*
    ** We need to create a table which will contain a row for every row to
    ** be returned to the client.
    */

    create table #column_priv1(
        COLUMN_NAME             varchar(32) NOT NULL,
        grantor                 smallint NOT NULL,
        grantee                 smallint NOT NULL,
        select_privilege        bit,
        select_grantable        bit,
        insert_privilege        bit,
        insert_grantable        bit,
        update_privilege        bit,
        update_grantable        bit,
        references_privilege    bit,
        references_grantable    bit,
        uid                     smallint NOT NULL,
        gid                     smallint NOT NULL)

/*
** insert a row for the table owner (who has all permissions)
*/
    select @owner_uid = (
        select uid
        from sysobjects
        where id = @table_id)

    insert into #column_priv1
        select
            c.name,
            u.uid,
            @owner_uid,
            0,
            1,
            0,
            1,
            0,
            1,
            0,
            1,
            @owner_uid,
            0
        from syscolumns c, sysusers u
        where id = @table_id
            and c.number = 0
            and u.suid = 1        /* grantor is dbo of database */
/*
** now stick in a row for every column for every user in the database
** we will need to weed out those who have no permissions later
** (and yes this is a cartesion product: the uid field in sysprotects
** can also have a group id, in which case we need to extend those
** privileges to all group members).
*/

    insert into #column_priv1
        select distinct
            c.name,
            o.uid,
            u.uid,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            u.uid,
            u.gid
        from sysusers u, syscolumns c, sysobjects o
        where o.id = @table_id
            and c.id = o.id
            and c.number = 0
            and u.gid <> u.uid
            and u.uid <> @owner_uid

    /*
    ** we need to create another temporary table to contain all the various
    ** protection information for the table in question
    */
    create table #protects (
                uid         smallint NOT NULL,
                grantor        smallint NOT NULL,
                action        tinyint NOT NULL,
                protecttype tinyint NOT NULL,
                name        varchar(32) NOT NULL)

    insert into #protects
        select
            p.uid,
            p.uid,
            p.action,
            p.protecttype,
            isnull(col_name(id, c.number), '~All')
            from
                sysprotects p,
                master.dbo.spt_values c,
                master.dbo.spt_values a,
                master.dbo.spt_values b
            where
                convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
                    & c.high <> 0
                    and c.number <= (
                        select count(*)
                        from syscolumns
                        where id = @table_id)
                and c.type = 'P'
                and a.type = 'T'
                and a.number = p.action
                and p.action in (193,195,197,26)
                and b.type = 'T'
                and b.number = p.protecttype
                and p.id = @table_id
                and p.uid between @low and @high


    update #column_priv1
    set select_privilege = 1
        from #protects p
    where
        p.protecttype = 205
        and p.action = 193
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
            where
                protecttype = 206
                and action = 193
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and ( uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set insert_privilege = 1
        from #protects p
    where
        p.protecttype = 205
        and p.action = 195
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
            where
                protecttype = 206
                and action = 195
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and (uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set update_privilege = 1
        from #protects p
    where
        p.protecttype = 205
        and p.action = 197
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
                where protecttype = 206
                and action = 197
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and (uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set references_privilege = 1
        from #protects p
    where
        p.protecttype = 205
        and p.action = 26
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
                where protecttype = 206
                and action = 26
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and (uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set select_grantable = 1
        from #protects p
    where
        p.protecttype = 204
        and p.action = 193
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
            where
                protecttype = 206
                and action = 193
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and ( uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set insert_grantable = 1
        from #protects p
    where
        p.protecttype = 204
        and p.action = 195
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
            where
                protecttype = 206
                and action = 195
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and ( uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set update_grantable = 1
        from #protects p
    where
        p.protecttype = 204
        and p.action = 197
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
            where
                protecttype = 206
                and action = 197
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and ( uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    update #column_priv1
    set references_grantable = 1
        from #protects p
    where
        p.protecttype = 204
        and p.action = 26
        and (p.name = #column_priv1.COLUMN_NAME
            or name = '~All')
        and (p.uid = 0
            or p.uid = #column_priv1.gid
            or p.uid = #column_priv1.uid)
        and not exists (
            select * from #protects
            where
                protecttype = 206
                and action = 26
                and (name = #column_priv1.COLUMN_NAME
                    or name = '~All')
                and ( uid = 0
                    or uid = #column_priv1.gid
                    or uid = #column_priv1.uid))

    create table #column_priv2(
        COLUMN_NAME     varchar(32) NOT NULL,
        grantor         smallint NULL,
        grantee         smallint NOT NULL,
        PRIVILEGE        varchar(32) NOT NULL,
        IS_GRANTABLE    varchar(3) NULL)

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'SELECT',
            'NO'
        from #column_priv1
        where select_privilege = 1 and select_grantable    = 0

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'INSERT',
            'NO'
        from #column_priv1
        where insert_privilege = 1 and insert_grantable = 0

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'UPDATE',
            'NO'
        from #column_priv1
        where update_privilege = 1 and update_grantable = 0

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'REFERENCES',
            'NO'
        from #column_priv1
        where references_privilege = 1 and references_grantable = 0

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'SELECT',
            'YES'
        from #column_priv1
        where select_grantable = 1

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'INSERT',
            'YES'
        from #column_priv1
        where insert_grantable = 1

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'UPDATE',
            'YES'
        from #column_priv1
        where update_grantable = 1

    insert into #column_priv2
        select
            COLUMN_NAME,
            grantor,
            grantee,
            'REFERENCES',
            'YES'
        from #column_priv1
        where references_grantable = 1

    select
        convert(varchar(32),db_name()) TABLE_QUALIFIER,
        convert(varchar(32),user_name(@owner_uid)) TABLE_OWNER,
        @table_name TABLE_NAME,
        COLUMN_NAME,
        convert(varchar(32),user_name(grantor)) GRANTOR,
        convert(varchar(32),user_name(grantee)) GRANTEE,
        PRIVILEGE,
        IS_GRANTABLE
    from #column_priv2
    where COLUMN_NAME like @column_name
    order by 4, 7
go

if    (charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.50 SQL Server.'
    print 'Ignore the following errors.'
end
else
    drop proc sp_column_privileges
go

/*    Procedure for 6.50 server */
CREATE PROCEDURE sp_column_privileges (
            @table_name         varchar(32),
            @table_owner        varchar(32) = null,
            @table_qualifier    varchar(32) = null,
            @column_name        varchar(90) = null)
as

    declare @table_id     int

    if @column_name is null /*    If column name not supplied, match all */
        select @column_name = '%'

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end
    if @table_owner is null
    begin    /* If unqualified table name */
        select @table_id = object_id(@table_name)
    end
    else
    begin    /* Qualified table name */
        select @table_id = object_id(@table_owner + '.' + @table_name)
    end

    select
        convert(varchar(32),db_name()) TABLE_QUALIFIER,
        convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
        @table_name TABLE_NAME,
        convert(varchar(32),c.name) COLUMN_NAME,
        convert(varchar(32),user_name(p.grantor)) GRANTOR,
        convert(varchar(32),user_name(u.uid)) GRANTEE,
        case p.action
             when 193 then 'SELECT'
             when 195 then 'INSERT'
             when 197 then 'UPDATE'
             else convert(varchar(32),'REFERENCES')
        end PRIVILEGE,
        case when p.protecttype = 205 then 'NO'
            else 'YES'
        end IS_GRANTABLE
    from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c
    where
        c.id = @table_id
        and c.name like @column_name
        and c.id = p.id
        and c.id = o.id
        and case substring(p.columns, 1, 1) & 1
                when NULL then 255    /* all columns have permission */
                when 0 then convert(tinyint, substring(p.columns, v.low, 1))
                else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
            end
            & v.high <> 0            /* permission applies to this column */
        and v.number <= (select count(*) from syscolumns
            where id = @table_id)    /* ranges from 1 to # of columns in table */
        and v.type = 'P'
        and v.number = c.colid
            /* expand groups */
        and ((p.uid = u.uid and u.uid <> u.gid) or
             (p.uid = u.gid and u.uid <> u.gid))
        and p.protecttype <> 206    /* only grant rows */
        and p.action in (26,193,195,197)
        and o.uid <> u.uid            /* no rows for owner */
        and not exists (            /* exclude revoke'd privileges */
            select *
            from sysprotects p1
            where
                p1.protecttype = 206
                and p1.action = p.action
                and p1.id = p.id
                and p1.uid = u.uid
                and case substring(p1.columns, 1, 1) & 1
                        when NULL then 255    /* all columns have permission */
                        when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
                        else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
                    end
                    & v.high <> 0)            /* permission applies to this column */
    union all
    select    /*    Add rows for table owner */
        convert(varchar(32),db_name()) TABLE_QUALIFIER,
        convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
        @table_name TABLE_NAME,
        convert(varchar(32),col_name(@table_id, c.colid)) COLUMN_NAME,
        convert(varchar(32),user_name(u.uid)) grantor,
        convert(varchar(32),user_name(o.uid)) grantee,
        case v.number
            when 193 then 'SELECT'
            when 195 then 'INSERT'
            when 197 then 'UPDATE'
            else convert(varchar(32),'REFERENCES')
        end PRIVILEGE,
        convert(varchar,'YES') IS_GRANTABLE
    from sysobjects o, spt_values v, sysusers u, syscolumns c
    where
        c.id = @table_id
        and c.name like @column_name
        and c.id = o.id
        and u.suid = 1        /* grantor is dbo of database */
        and v.type = 'P'    /* cross product to get all exposed privileges */
        and v.number in (26,193,195,197)
        and not exists (    /* exclude revoke'd privileges */
            select *
            from sysprotects p1
            where
                p1.protecttype = 206
                and p1.action = v.number
                and p1.id = o.id
                and p1.uid = o.uid)
    order by 4, 7
go

grant execute on sp_column_privileges to public
go

dump tran master with no_log
go

print 'creating sp_columns'
go

/*    Procedure for pre-6.0 server */
CREATE PROCEDURE sp_columns (
                 @table_name        varchar(90),
                 @table_owner        varchar(90) = null,
                 @table_qualifier    varchar(90) = null,
                 @column_name        varchar(90) = null,
                 @ODBCVer            int = 2)
AS
    DECLARE @full_table_name    char(181)
    DECLARE @table_id int

    if @ODBCVer <> 3
        select @ODBCVer = 2
    if @column_name is null /*    If column name not supplied, match all */
        select @column_name = '%'
    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end
    if @table_name is null
    begin    /*    If table name not supplied, match all */
        select @table_name = '%'
    end
    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = @table_name
    end
    else
    begin    /* Qualified table name */
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end

    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)
    if ((charindex('%',@full_table_name) = 0) and
        (charindex('_',@full_table_name) = 0)  and
        @table_id <> 0)
    begin
        /* this block is for the case where there is no pattern
             matching required for the table name */
        SELECT
            TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
            TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            TABLE_NAME = convert(varchar(32),o.name),
            COLUMN_NAME = convert(varchar(32),c.name),
            d.DATA_TYPE,
            TYPE_NAME = t.name,
            "PRECISION" = isnull(d.data_precision, convert(int,c.length)),
            LENGTH = isnull(d.length, convert(int,c.length)),
            SCALE = d.numeric_scale,
            d.RADIX,
            NULLABLE =    /* set nullability from status flag */
                convert(smallint, convert(bit, c.status&8)),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),substring(text,2,datalength(text)-2)),
            DATETIME_CODE = convert(int,d.SQL_DATETIME_SUB),
            CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO      YES',(c.status&8)+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            syscomments m,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.id = @table_id
            AND c.id = o.id
            AND t.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND o.type <> 'P'
            AND c.usertype = t.usertype
            AND c.name like @column_name
            AND c.cdefault *= m.id
        ORDER BY 16
    end
    else
    begin
        /* this block is for the case where there IS pattern
             matching done on the table name */
        if @table_owner is null /*    If owner not supplied, match all */
            select @table_owner = '%'
        SELECT
            TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
            TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            TABLE_NAME = convert(varchar(32),o.name),
            COLUMN_NAME = convert(varchar(32),c.name),
            d.DATA_TYPE,
            TYPE_NAME = t.name,
            "PRECISION" = isnull(d.data_precision, convert(int,c.length)),
            LENGTH = isnull(d.length, convert(int,c.length)),
            SCALE = d.numeric_scale,
            d.RADIX,
            NULLABLE =    /* set nullability from status flag */
                convert(smallint, convert(bit, c.status&8)),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),substring(text,2,datalength(text)-2)),
            DATETIME_CODE = convert(int,d.SQL_DATETIME_SUB),
            CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO      YES',(c.status&8)+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            syscomments m,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.name like @table_name
            AND user_name(o.uid) like @table_owner
            AND o.id = c.id
            AND t.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND o.type <> 'P'
            AND c.usertype = t.usertype
            AND c.name like @column_name
            AND c.cdefault *= m.id
        ORDER BY 2, 3, 16
    end
go

if    (charindex('6.00', @@version) = 0 and
     charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.0 SQL Server.'
    print 'Ignore the following error.'
end
else
    drop proc sp_columns
go

/*    Procedure for 6.0 server */
CREATE PROCEDURE sp_columns (
                 @table_name        varchar(90),
                 @table_owner        varchar(90) = null,
                 @table_qualifier    varchar(90) = null,
                 @column_name        varchar(90) = null,
                 @ODBCVer            int = 2)
AS
    DECLARE @full_table_name    char(181)
    DECLARE @table_id int

    if @ODBCVer <> 3
        select @ODBCVer = 2
    if @column_name is null /*    If column name not supplied, match all */
        select @column_name = '%'
    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror (15250, -1,-1,'Table')
            return
        end
    end
    if @table_name is null
    begin    /*    If table name not supplied, match all */
        select @table_name = '%'
    end
    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = @table_name
    end
    else
    begin    /* Qualified table name */
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end

    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)
    if ((charindex('%',@full_table_name) = 0) and
        (charindex('_',@full_table_name) = 0)  and
        @table_id <> 0)
    begin
        /* this block is for the case where there is no pattern
            matching required for the table name */
        SELECT
            TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
            TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            TABLE_NAME = convert(varchar(32),o.name),
            COLUMN_NAME = convert(varchar(32),c.name),
            d.DATA_TYPE,
            case
                when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
                else d.TYPE_NAME
            end TYPE_NAME,
            case
                when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
                else isnull(convert(int,c.prec), 2147483647)
            end "PRECISION",
            case
                when d.ss_dtype IN (106, 108, 55, 63) then    /* decimal/numeric types */
                    convert(int,c.prec+2)
                else
                    isnull(d.length, convert(int,c.length))
            end LENGTH,
            SCALE = convert(smallint, c.scale),
            d.RADIX,
            NULLABLE =    /* set nullability from status flag */
                convert(smallint, convert(bit, c.status&8)),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),substring(text,2,datalength(text)-2)),
            DATETIME_CODE = convert(int,d.SQL_DATETIME_SUB),
            CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647)+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO      YES',(c.status&8)+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            syscomments m,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.id = @table_id
            AND c.id = o.id
            AND t.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND o.type <> 'P'
            AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128
            AND c.usertype = t.usertype
            AND c.name like @column_name
            AND c.cdefault *= m.id
        ORDER BY 16
    end
    else
    begin
        /* this block is for the case where there IS pattern
            matching done on the table name */
        if @table_owner is null /*    If owner not supplied, match all */
            select @table_owner = '%'
        SELECT
            TABLE_QUALIFIER = convert(varchar(32),DB_NAME()),
            TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            TABLE_NAME = convert(varchar(32),o.name),
            COLUMN_NAME = convert(varchar(32),c.name),
            d.DATA_TYPE,
            case
                when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
                else d.TYPE_NAME
            end TYPE_NAME,
            case
                when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
                else isnull(convert(int,c.prec), 2147483647)
            end "PRECISION",
            case
                when d.ss_dtype IN (106, 108, 55, 63) then    /* decimal/numeric types */
                    convert(int,c.prec+2)
                else
                    isnull(d.length, convert(int,c.length))
            end LENGTH,
            SCALE = convert(smallint, c.scale),
            d.RADIX,
            NULLABLE =    /* set nullability from status flag */
                convert(smallint, convert(bit, c.status&8)),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),substring(text,2,datalength(text)-2)),
            DATETIME_CODE = convert(int,d.SQL_DATETIME_SUB),
            CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647)+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO      YES',(c.status&8)+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            syscomments m,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.name like @table_name
            AND user_name(o.uid) like @table_owner
            AND o.id = c.id
            AND t.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND o.type <> 'P'
            AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128
            AND c.usertype = t.usertype
            AND c.name like @column_name
            AND c.cdefault *= m.id
        ORDER BY 2, 3, 16
    end
go

grant execute on sp_columns to public
go

dump tran master with no_log
go

print 'creating sp_databases'
go

create proc sp_databases
as
    /* Use temporary table to sum up database size w/o using group by */
    create table #databases (
                  DATABASE_NAME varchar(32) NOT NULL,
                  size int NOT NULL)

    /* Insert row for each database */
    insert into #databases
        select
            name,
            (select sum(size) from master.dbo.sysusages
                where dbid = d.dbid)
        from master.dbo.sysdatabases d

    select
         convert(varchar(32),DATABASE_NAME),
         DATABASE_SIZE = size*2,    /* Convert from 2048 byte pages to K */
         REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
    from #databases
    order by 1
go

grant execute on sp_databases to public
go

dump tran master with no_log
go

print 'creating sp_datatype_info'
go

/*    Procedure for pre-6.0 server */
create proc sp_datatype_info
    (@data_type int = 0, @ODBCVer tinyint = 2)
as
    if @ODBCVer <> 3
        select @ODBCVer = 2
    if @data_type = 0
        select
            TYPE_NAME = t.name,
            d.DATA_TYPE,
            "PRECISION" = isnull(d.data_precision, convert(int,t.length)),
            d.LITERAL_PREFIX,
            d.LITERAL_SUFFIX,
            e.CREATE_PARAMS,
            d.NULLABLE,
            d.CASE_SENSITIVE,
            d.SEARCHABLE,
            d.UNSIGNED_ATTRIBUTE,
            d.MONEY,
            d.AUTO_INCREMENT,
            LOCAL_TYPE_NAME = t.name,
            MINIMUM_SCALE = d.numeric_scale,
            MAXIMUM_SCALE = d.numeric_scale,
            d.SQL_DATA_TYPE,
            d.SQL_DATETIME_SUB,
            NUM_PREC_RADIX = convert(int,d.RADIX),
            USERTYPE = t.usertype
        from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
        where
            d.ss_dtype = t.type
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            and t.usertype *= e.user_type
            and t.type not in (111,109,38,110)    /* get rid of nullable types */
        order by 2, 12, 11, t.usertype

    else
        select
            TYPE_NAME = t.name,
            d.DATA_TYPE,
            "PRECISION" = isnull(d.data_precision, convert(int,t.length)),
            d.LITERAL_PREFIX,
            d.LITERAL_SUFFIX,
            e.CREATE_PARAMS,
            d.NULLABLE,
            d.CASE_SENSITIVE,
            d.SEARCHABLE,
            d.UNSIGNED_ATTRIBUTE,
            d.MONEY,
            d.AUTO_INCREMENT,
            LOCAL_TYPE_NAME = t.name,
            MINIMUM_SCALE = d.numeric_scale,
            MAXIMUM_SCALE = d.numeric_scale,
            d.SQL_DATA_TYPE,
            d.SQL_DATETIME_SUB,
            NUM_PREC_RADIX = convert(int,d.RADIX),
            USERTYPE = t.usertype
        from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
        where
            DATA_TYPE = @data_type
            and d.ss_dtype = t.type
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            and t.usertype *= e.user_type
            and t.type not in (111,109,38,110)    /* get rid of nullable types */
        order by 12, 11, t.usertype

go

if    (charindex('6.00', @@version) = 0 and
     charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.0 SQL Server.'
    print 'Ignore the following errors.'
end
else
    drop proc sp_datatype_info
go

/*    Procedure for 6.0 server */
create proc sp_datatype_info
    (@data_type int = 0, @ODBCVer tinyint = 2)
as
    if @ODBCVer <> 3
        select @ODBCVer = 2
    if @data_type = 0
        select
            case
                when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
                else d.TYPE_NAME
            end TYPE_NAME,
            d.DATA_TYPE,
            case
                when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
                when d.ss_dtype in (35,34) then 2147483647                /* TEXT/IMAGE */
                when d.ss_dtype in (55,63,106,108) then @@max_precision /* DECIMAL/NUMERIC */
                else convert(int,t.prec)
            end "PRECISION",
            d.LITERAL_PREFIX,
            d.LITERAL_SUFFIX,
            e.CREATE_PARAMS,
            case
                when d.AUTO_INCREMENT = 1 then convert(smallint,0) /* IDENTITY*/
                else convert(smallint,t.allownulls)
            end NULLABLE,
            d.CASE_SENSITIVE,
            d.SEARCHABLE,
            d.UNSIGNED_ATTRIBUTE,
            d.MONEY,
            d.AUTO_INCREMENT,
            case
                when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
                else d.TYPE_NAME
            end LOCAL_TYPE_NAME,
            MINIMUM_SCALE = d.numeric_scale,
            case
                when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 then convert(smallint,@@max_precision) /* DECIMAL/NUMERIC */
                when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1 then convert(smallint,0) /* DECIMAL/NUMERIC IDENTITY*/
                else convert(smallint,t.scale)
            end MAXIMUM_SCALE,
            d.SQL_DATA_TYPE,
            d.SQL_DATETIME_SUB,
            NUM_PREC_RADIX = convert(int,d.RADIX),
            USERTYPE = t.usertype
        from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
        where
            d.ss_dtype = t.type
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            and t.usertype *= e.user_type
            and isnull(d.AUTO_INCREMENT,0) *= e.AUTO_INCREMENT
            and t.type not in (111,109,38,110,55,63)    /* get rid of nullable types */
        order by 2, 12, 11, t.usertype

    else
        select
            case
                when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
                else d.TYPE_NAME
            end TYPE_NAME,
            d.DATA_TYPE,
            case
                when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
                when d.ss_dtype in (35,34) then 2147483647                /* TEXT/IMAGE */
                when d.ss_dtype in (55,63,106,108) then @@max_precision /* DECIMAL/NUMERIC */
                else convert(int,t.prec)
            end "PRECISION",
            d.LITERAL_PREFIX,
            d.LITERAL_SUFFIX,
            e.CREATE_PARAMS,
            case
                when d.AUTO_INCREMENT = 1 then convert(smallint,0) /* IDENTITY*/
                else convert(smallint,t.allownulls)
            end NULLABLE,
            d.CASE_SENSITIVE,
            d.SEARCHABLE,
            d.UNSIGNED_ATTRIBUTE,
            d.MONEY,
            d.AUTO_INCREMENT,
            case
                when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
                else d.TYPE_NAME
            end LOCAL_TYPE_NAME,
            MINIMUM_SCALE = d.numeric_scale,
            case
                when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 then convert(smallint,@@max_precision) /* DECIMAL/NUMERIC */
                when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1 then convert(smallint,0) /* DECIMAL/NUMERIC IDENTITY*/
                else convert(smallint,t.scale)
            end MAXIMUM_SCALE,
            d.SQL_DATA_TYPE,
            d.SQL_DATETIME_SUB,
            NUM_PREC_RADIX = convert(int,d.RADIX),
            USERTYPE = t.usertype
        from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
        where
            d.DATA_TYPE = @data_type
            and d.ss_dtype = t.type
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            and t.usertype *= e.user_type
            and isnull(d.AUTO_INCREMENT,0) *= e.AUTO_INCREMENT
            and t.type not in (111,109,38,110,55,63)    /* get rid of nullable types */
        order by 12, 11, t.usertype
go

grant execute on sp_datatype_info to public
go

dump tran master with no_log
go

print 'creating sp_fkeys'
go

/*    Procedure for pre-6.0 server */
CREATE PROCEDURE sp_fkeys(
               @pktable_name        varchar(32) = null,
               @pktable_owner        varchar(32) = null,
               @pktable_qualifier    varchar(32) = null,
               @fktable_name        varchar(32) = null,
               @fktable_owner        varchar(32) = null,
               @fktable_qualifier    varchar(32) = null )
as
    declare    @order_by_pk int

    select  @order_by_pk = 0

    if (@pktable_name is null) and (@fktable_name is null)
    begin    /* If neither primary key nor foreign key table names given */
        raiserror 20004 'PK table name or FK table name must be given.'
        return
    end
    if @fktable_qualifier is not null
    begin
        if db_name() <> @fktable_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Foreign Key Table qualifier must be name of current database'
            return
        end
    end
    if @pktable_qualifier is not null
    begin
        if db_name() <> @pktable_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Primary Key Table qualifier must be name of current database'
            return
        end
    end

    if @pktable_name is null
    begin /*  If table name not supplied, match all */
        select @pktable_name = '%'
        select @order_by_pk = 1
    end
    if @pktable_owner is null    /*    If PK owner not supplied, match all */
        select @pktable_owner = '%'
    if @fktable_name is null    /*    If table name not supplied, match all */
        select @fktable_name = '%'
    if @fktable_owner is null    /*    If FK owner not supplied, match all */
        select @fktable_owner = '%'

    if @@trancount <> 0
    begin    /* If inside a transaction */
        raiserror 20003 'The procedure ''sp_fkeys'' cannot be executed from within a transaction.'
        return
    end
    create table #fkeys(
             PKTABLE_QUALIFIER    varchar(32) NULL,
             PKTABLE_OWNER        varchar(32) NULL,
             PKTABLE_NAME        varchar(32) NOT NULL,
             PKCOLUMN_NAME        varchar(32) NOT NULL,
             FKTABLE_QUALIFIER    varchar(32) NULL,
             FKTABLE_OWNER        varchar(32) NULL,
             FKTABLE_NAME        varchar(32) NOT NULL,
             FKCOLUMN_NAME        varchar(32) NOT NULL,
             KEY_SEQ            smallint NOT NULL)

    /*    SQL Server supports upto 8 PK/FK relationships between 2 tables */
    /*    Process syskeys for each relationship */
    /*    The inserts below adds a row to the temp table for each of the
        8 possible relationships */
    insert into #fkeys
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            1
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key1
            and c2.id = k.depid
            and c2.colid = k.depkey1
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            2
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key2
            and c2.id = k.depid
            and c2.colid = k.depkey2
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            3
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key3
            and c2.id = k.depid
            and c2.colid = k.depkey3
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            4
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key4
            and c2.id = k.depid
            and c2.colid = k.depkey4
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            5
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key5
            and c2.id = k.depid
            and c2.colid = k.depkey5
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            6
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key6
            and c2.id = k.depid
            and c2.colid = k.depkey6
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            7
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key7
            and c2.id = k.depid
            and c2.colid = k.depkey7
            and o1.id = k.depid
            and o2.id = k.id
    union all
        select
            db_name(),
            user_name(o1.uid),
            object_name(k.depid),
            c2.name,
            db_name(),
            user_name(o2.uid),
            object_name(k.id),
            c1.name,
            8
        from
            syskeys k, syscolumns c1, syscolumns c2,sysobjects o1, sysobjects o2
        where
            c1.id = k.id
            and k.type = 2    /* Foreign type key */
            and c1.colid = k.key8
            and c2.id = k.depid
            and c2.colid = k.depkey8
            and o1.id = k.depid
            and o2.id = k.id

    if @order_by_pk = 1 /*    If order by PK fields */
        select
            PKTABLE_QUALIFIER,
            PKTABLE_OWNER,
            PKTABLE_NAME,
            PKCOLUMN_NAME,
            FKTABLE_QUALIFIER,
            FKTABLE_OWNER,
            FKTABLE_NAME,
            FKCOLUMN_NAME,
            KEY_SEQ,
            UPDATE_RULE = convert(smallint, null),
            DELETE_RULE = convert(smallint,null),
            FK_NAME = convert(varchar(32),null),
            PK_NAME = convert(varchar(32),null)
        from #fkeys
        where FKTABLE_NAME like @fktable_name
            and FKTABLE_OWNER like @fktable_owner
            and PKTABLE_NAME  like @pktable_name
            and PKTABLE_OWNER like @pktable_owner
        order by 1, 2, 3, 9
    else        /*    Order by FK fields */
        select
            PKTABLE_QUALIFIER,
            PKTABLE_OWNER,
            PKTABLE_NAME,
            PKCOLUMN_NAME,
            FKTABLE_QUALIFIER,
            FKTABLE_OWNER,
            FKTABLE_NAME,
            FKCOLUMN_NAME,
            KEY_SEQ,
            UPDATE_RULE = convert(smallint,null),
            DELETE_RULE = convert(smallint,null),
            FK_NAME = convert(varchar(32),null),
            PK_NAME = convert(varchar(32),null)
        from #fkeys
        where FKTABLE_NAME like @fktable_name
            and FKTABLE_OWNER like @fktable_owner
            and PKTABLE_NAME  like @pktable_name
            and PKTABLE_OWNER like @pktable_owner
        order by 5, 6, 7, 9
go

if    (charindex('6.00', @@version) = 0 and
     charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.0 SQL Server.'
    print 'Ignore the following errors.'
end
else
    drop proc sp_fkeys
go

/*    Procedure for 6.0 server */
CREATE PROCEDURE sp_fkeys(
               @pktable_name        varchar(32) = null,
               @pktable_owner        varchar(32) = null,
               @pktable_qualifier    varchar(32) = null,
               @fktable_name        varchar(32) = null,
               @fktable_owner        varchar(32) = null,
               @fktable_qualifier    varchar(32) = null )
as
    DECLARE @pktable_id            int
    DECLARE @pkfull_table_name    char(70)
    DECLARE @fktable_id            int
    DECLARE @fkfull_table_name    char(70)
    declare    @order_by_pk        int

    select  @order_by_pk = 0

    if (@pktable_name is null) and (@fktable_name is null)
    begin    /* If neither primary key nor foreign key table names given */
        raiserror (15252,-1,-1)
        return
    end

    if @pktable_owner is null
    begin    /* If unqualified primary key table name */
        SELECT @pkfull_table_name = @pktable_name
    end
    else
    begin    /* Qualified primary key table name */
        SELECT @pkfull_table_name = @pktable_owner + '.' + @pktable_name
    end
    /*    Get Object ID */
    SELECT @pktable_id = object_id(@pkfull_table_name)

    if @fktable_owner is null
    begin    /* If unqualified foreign key table name */
        SELECT @fkfull_table_name = @fktable_name
    end
    else
    begin    /* Qualified foreign key table name */
        SELECT @fkfull_table_name = @fktable_owner + '.' + @fktable_name
    end
    /*    Get Object ID */
    SELECT @fktable_id = object_id(@fkfull_table_name)

    if @fktable_name is not null
    begin
        if @fktable_id is null
            SELECT @fktable_id = 0    /* fk table not found, empty result */
    end

    if @pktable_name is null
    begin /*  If table name not supplied, match all */
        select @order_by_pk = 1
    end
    else
    begin
        if @pktable_id is null
        begin
            SELECT @pktable_id = 0    /* pk table not found, empty result */
        end
    end

    if (@@trancount <> 0 and charindex('6.50', @@version) = 0)
    begin    /* If inside a transaction */
        raiserror(15002,-1,-1,'sp_fkeys')
        return
    end

    create table #fkeys(
             pkdb_id        int NOT NULL,
             pktable_id     int NOT NULL,
             pkcolid        int NOT NULL,
             fkdb_id        int NOT NULL,
             fktable_id        int NOT NULL,
             fkcolid        int NOT NULL,
             KEY_SEQ        smallint NOT NULL,
             fk_id            int NOT NULL,
             pk_id            int NOT NULL)

    /*    SQL Server supports upto 16 PK/FK relationships between 2 tables */
    /*    Process syskeys for each relationship */
    /*    The inserts below adds a row to the temp table for each of the
        16 possible relationships */
    insert into #fkeys
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey1,
            r.fkeydbid,
            r.fkeyid,
            r.fkey1,
            1,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey2,
            r.fkeydbid,
            r.fkeyid,
            r.fkey2,
            2,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey3,
            r.fkeydbid,
            r.fkeyid,
            r.fkey3,
            3,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey4,
            r.fkeydbid,
            r.fkeyid,
            r.fkey4,
            4,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey5,
            r.fkeydbid,
            r.fkeyid,
            r.fkey5,
            5,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey6,
            r.fkeydbid,
            r.fkeyid,
            r.fkey6,
            6,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey7,
            r.fkeydbid,
            r.fkeyid,
            r.fkey7,
            7,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey8,
            r.fkeydbid,
            r.fkeyid,
            r.fkey8,
            8,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey9,
            r.fkeydbid,
            r.fkeyid,
            r.fkey9,
            9,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey10,
            r.fkeydbid,
            r.fkeyid,
            r.fkey10,
            10,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey11,
            r.fkeydbid,
            r.fkeyid,
            r.fkey11,
            11,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey12,
            r.fkeydbid,
            r.fkeyid,
            r.fkey12,
            12,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey13,
            r.fkeydbid,
            r.fkeyid,
            r.fkey13,
            13,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey14,
            r.fkeydbid,
            r.fkeyid,
            r.fkey14,
            14,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey15,
            r.fkeydbid,
            r.fkeyid,
            r.fkey15,
            15,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
      union all
        select
            r.rkeydbid,
            r.rkeyid,
            r.rkey16,
            r.fkeydbid,
            r.fkeyid,
            r.fkey16,
            16,
            r.constid,
            s.constid
        from
            sysreferences r, sysconstraints s, sysconstraints s1
        where    r.rkeyid = s.id
            AND (s.status & 0xf) = 1
            AND r.constid = s1.constid
            AND (s1.status & 0x400f) = 3
            AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)

    if @order_by_pk = 1 /*    If order by PK fields */
        select
            PKTABLE_QUALIFIER = convert(varchar(32),DB_NAME(f.pkdb_id)),
            PKTABLE_OWNER = convert(varchar(32),USER_NAME(o1.uid)),
            PKTABLE_NAME = convert(varchar(32),o1.name),
            PKCOLUMN_NAME = convert(varchar(32),c1.name),
            FKTABLE_QUALIFIER = convert(varchar(32),DB_NAME(f.fkdb_id)),
            FKTABLE_OWNER = convert(varchar(32),USER_NAME(o2.uid)),
            FKTABLE_NAME = convert(varchar(32),o2.name),
            FKCOLUMN_NAME = convert(varchar(32),c2.name),
            KEY_SEQ,
            UPDATE_RULE = convert(smallint,1),
            DELETE_RULE = convert(smallint,1),
            FK_NAME = convert(varchar(128),OBJECT_NAME(fk_id)),
            PK_NAME = convert(varchar(128),OBJECT_NAME(pk_id))
        from #fkeys f,
            sysobjects o1, sysobjects o2,
            syscolumns c1, syscolumns c2
        where    o1.id = f.pktable_id
            AND o2.id = f.fktable_id
            AND c1.id = f.pktable_id
            AND c2.id = f.fktable_id
            AND c1.colid = f.pkcolid
            AND c2.colid = f.fkcolid
        order by 1,2,3,9
    else        /*    Order by FK fields */
        select
            PKTABLE_QUALIFIER = convert(varchar(32),DB_NAME(f.pkdb_id)),
            PKTABLE_OWNER = convert(varchar(32),USER_NAME(o1.uid)),
            PKTABLE_NAME = convert(varchar(32),o1.name),
            PKCOLUMN_NAME = convert(varchar(32),c1.name),
            FKTABLE_QUALIFIER = convert(varchar(32),DB_NAME(f.fkdb_id)),
            FKTABLE_OWNER = convert(varchar(32),USER_NAME(o2.uid)),
            FKTABLE_NAME = convert(varchar(32),o2.name),
            FKCOLUMN_NAME = convert(varchar(32),c2.name),
            KEY_SEQ,
            UPDATE_RULE = convert(smallint,1),
            DELETE_RULE = convert(smallint,1),
            FK_NAME = convert(varchar(128),OBJECT_NAME(fk_id)),
            PK_NAME = convert(varchar(128),OBJECT_NAME(pk_id))
        from #fkeys f,
            sysobjects o1, sysobjects o2,
            syscolumns c1, syscolumns c2
        where    o1.id = f.pktable_id
            AND o2.id = f.fktable_id
            AND c1.id = f.pktable_id
            AND c2.id = f.fktable_id
            AND c1.colid = f.pkcolid
            AND c2.colid = f.fkcolid
        order by 5,6,7,9
go

grant execute on sp_fkeys to public
go

dump tran master with no_log
go

print 'creating sp_pkeys'
go

/*    Procedure for pre-6.0 server */
CREATE PROCEDURE sp_pkeys(
               @table_name        varchar(32),
               @table_owner     varchar(32) = null,
               @table_qualifier varchar(32) = null )
as
    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end
    if @table_owner is null /*    If owner not supplied, match all */
        select @table_owner = '%'
    if @@trancount <> 0
    begin    /* If inside a transaction */
        raiserror 20003 'The procedure ''sp_pkeys'' cannot be executed from within a transaction.'
        return
    end

    create table #pkeys(
             TABLE_QUALIFIER varchar(32) NULL,
             TABLE_OWNER     varchar(32) NULL,
             TABLE_NAME      varchar(32) NOT NULL,
             COLUMN_NAME     varchar(32) NOT NULL,
             KEY_SEQ         smallint NOT NULL)

    /*    SQL Server supports upto 8 PK/FK relationships between 2 tables */
    /*    Process syskeys for each relationship */
    /*    The inserts below adds a row to the temp table for each of the
        8 possible relationships */
    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            1
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = k.key1
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            2
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = key2
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            3
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = key3
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            4
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = key4
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            5
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = key5
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            6
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = key6
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.id),
            object_name(k.id),
            c.name,
            7
        from
            syskeys k, syscolumns c
        where
            c.id = k.id
            and k.type = 1    /* Primary type key */
            and c.colid = key7
    if (@@rowcount = 0)
        goto done

    insert into #pkeys
         select
             db_name(),
             (select user_name(uid) from sysobjects o where o.id = k.id),
             object_name(k.id),
             c.name,
             8
         from
             syskeys k, syscolumns c
         where
             c.id = k.id
             and k.type = 1 /* Primary type key */
             and c.colid = key8

    done:
    select
        TABLE_QUALIFIER,
        TABLE_OWNER,
        TABLE_NAME,
        COLUMN_NAME,
        KEY_SEQ,
        PK_NAME = convert(varchar(32),null)
    from #pkeys
    where TABLE_NAME = @table_name
        and TABLE_OWNER like @table_owner
    order by 1, 2, 3, 5
go

if    (charindex('6.00', @@version) = 0 and
     charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.0 SQL Server.'
    print 'Ignore the following error.'
end
else
    drop proc sp_pkeys
go

/*    Procedure for 6.0 server */
CREATE PROCEDURE sp_pkeys(
               @table_name        varchar(32),
               @table_owner     varchar(32) = null,
               @table_qualifier varchar(32) = null )
as
    DECLARE @table_id            int
    DECLARE @full_table_name    char(70)

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror (15250, -1,-1,'Table')
            return
        end
    end
    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = @table_name
    end
    else
    begin    /* Qualified table name */
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)

    select
        TABLE_QUALIFIER = convert(varchar(32),db_name()),
        TABLE_OWNER = convert(varchar(32),user_name(o.uid)),
        TABLE_NAME = convert(varchar(32),o.name),
        COLUMN_NAME = convert(varchar(32),c.name),
        KEY_SEQ = convert(smallint,c1.colid),
        PK_NAME = convert(varchar(32),i.name)
    from
        sysindexes i, syscolumns c, sysobjects o, syscolumns c1
    where
        o.id = @table_id
        and o.id = c.id
        and o.id = i.id
        and (i.status & 0x800) = 0x800
        and c.name = index_col (@full_table_name, i.indid, c1.colid)
        and c1.colid <= i.keycnt    /* create rows from 1 to keycnt */
        and c1.id = @table_id
    order by 1, 2, 3, 5
go

grant execute on sp_pkeys to public
go

dump tran master with no_log
go

print 'creating sp_server_info'
go

create proc sp_server_info (
            @attribute_id  int = null)
as
    if @attribute_id is not null
        select *
        from master.dbo.spt_server_info
        where attribute_id = @attribute_id
    else
        select *
        from master.dbo.spt_server_info
        order by attribute_id
go

grant execute on sp_server_info to public
go

dump tran master with no_log
go

print 'creating sp_special_columns'
go

/*    Procedure for pre-6.0 server */
CREATE PROCEDURE sp_special_columns (
                 @table_name        varchar(32),
                 @table_owner        varchar(32) = null,
                 @table_qualifier    varchar(32) = null,
                 @col_type            char(1) = 'R',
                 @scope                char(1) = 'T',
                 @nullable            char(1) = 'U',
                 @ODBCVer            int = 2)
AS
    DECLARE @indid                int
    DECLARE @table_id            int
    DECLARE @full_table_name    char(70)
    DECLARE @msg                char(70)
    DECLARE @scopeout            smallint

    if @col_type not in ('R','V')
        begin
            raiserror 20002 'Illegal ''col_type'' specified -- must be ''R'' or ''V''.'
            return
        end

    if @scope = 'C'
        select @scopeout = 0
    else if @scope = 'T'
        select @scopeout = 1
    else
        begin
            raiserror 20002 'Illegal ''scope'' specified -- must be ''C'' or ''T''.'
            return
        end

    if @nullable not in ('U','O')
        begin
            raiserror 20002 'Illegal ''nullable'' specified -- must be ''U'' or ''O''.'
            return
        end

    if @table_qualifier is not null
       begin
          if db_name() <> @table_qualifier
              begin    /* If qualifier doesn't match current database */
                raiserror 20001 'Table qualifier must be name of current database'
                return
              end
       end
    if @table_owner is null
       begin    /* If unqualified table name */
          SELECT @full_table_name = @table_name
       end
    else
       begin    /* Qualified table name */
          SELECT @full_table_name = @table_owner + '.' + @table_name
       end
    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)

    if @col_type = 'V'
    BEGIN /* if ROWVER, just run that query */
        SELECT
            SCOPE = convert(smallint,NULL),
            COLUMN_NAME = convert(varchar(32),c.name),
            DATA_TYPE = convert(smallint, -3),
            TYPE_NAME = t.name,
            "PRECISION" = convert(int,8),
            LENGTH = convert(int,8),
            SCALE = convert(smallint, NULL),
            PSEUDO_COLUMN = convert(smallint,1)
        FROM
            systypes t, syscolumns c, master.dbo.spt_datatype_info d
        WHERE
            c.id = @table_id
            AND c.type = d.ss_dtype
            AND c.usertype = 80 /*    TIMESTAMP */
            AND t.usertype = 80 /*    TIMESTAMP */
        RETURN
    END

    /* ROWID, now find the id of the 'best' index for this table */

    IF @nullable = 'O'    /* Don't include any indexes that contain
                           nullable columns. */

            SELECT @indid = MIN(indid)
                FROM sysindexes i,syscolumns c,syscolumns c2
                WHERE
                    i.status&2 = 2        /*    If Unique Index */
                     AND c.id = i.id
                     AND c2.id = c.id
                     AND c2.colid < i.keycnt + (i.status&16)/16
                    AND i.id = @table_id
                    AND indid > 0        /*    Eliminate Table Row */
                    AND c.name = index_col(@table_name,i.indid,c2.colid)
                    GROUP BY indid HAVING SUM(c.status&8) = 0

    ELSE    /* Include indexes that are partially nullable. */

        SELECT @indid = MIN(indid)
            FROM sysindexes i
            WHERE
                status&2 = 2        /*    If Unique Index */
                AND id = @table_id
                AND indid > 0        /*    Eliminate Table Row */

    SELECT
        SCOPE = @scopeout,
        COLUMN_NAME = convert(varchar(32),INDEX_COL(@full_table_name,indid,c2.colid)),
        d.DATA_TYPE,
        TYPE_NAME = t.name,
        "PRECISION" = isnull(d.data_precision, convert(int,c.length)),
        LENGTH = isnull(d.length, convert(int,c.length)),
        SCALE = d.numeric_scale,
        PSEUDO_COLUMN = convert(smallint,1)
    FROM
        sysindexes x,
        syscolumns c,
        master.dbo.spt_datatype_info d,
        systypes t,
        syscolumns c2    /* Self-join to generate list of index columns and */
                        /* to extract datatype names */
    WHERE
        x.id = @table_id
        AND c.name = INDEX_COL(@full_table_name,@indid,c2.colid)
        AND c.id = x.id
        AND c2.id = x.id
        AND c2.colid < keycnt+(x.status&16)/16
        AND x.indid = @indid
        AND t.type = d.ss_dtype
        AND c.length = d.fixlen
        AND c.usertype = t.usertype

go

if    (charindex('6.00', @@version) = 0 and
     charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.0 SQL Server.'
    print 'Ignore the following errors.'
end
else
    drop proc sp_special_columns
go

/*    Procedure for 6.0 server */
CREATE PROCEDURE sp_special_columns (
                 @table_name        varchar(32),
                 @table_owner        varchar(32) = null,
                 @table_qualifier    varchar(32) = null,
                 @col_type            char(1) = 'R',
                 @scope                char(1) = 'T',
                 @nullable            char(1) = 'U',
                 @ODBCVer            int = 2)
AS
    DECLARE @indid                int
    DECLARE @table_id            int
    DECLARE @full_table_name    char(70)
    DECLARE @msg                char(70)
    DECLARE @scopeout            smallint

    if @col_type not in ('R','V')
        begin
            raiserror (15251,-1,-1,'col_type','''R'' or ''V''')
            return
        end

    if @scope = 'C'
        select @scopeout = 0
    else if @scope = 'T'
        select @scopeout = 1
    else
        begin
            raiserror (15251,-1,-1,'scope','''C'' or ''T''')
            return
        end

    if @nullable not in ('U','O')
        begin
            raiserror (15251,-1,-1,'nullable','''U'' or ''O''')
            return
        end

    if @table_qualifier is not null
       begin
          if db_name() <> @table_qualifier
              begin    /* If qualifier doesn't match current database */
                raiserror (15250, -1,-1,'Table')
                return
              end
       end
    if @table_owner is null
       begin    /* If unqualified table name */
          SELECT @full_table_name = @table_name
       end
    else
       begin    /* Qualified table name */
          SELECT @full_table_name = @table_owner + '.' + @table_name
       end
    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)

    if @col_type = 'V'
    BEGIN /* if ROWVER, just run that query */
        SELECT
            SCOPE = convert(smallint,NULL),
            COLUMN_NAME = convert(varchar(32),c.name),
            DATA_TYPE = convert(smallint, -2),
            TYPE_NAME = t.name,
            "PRECISION" = convert(int,8),
            LENGTH = convert(int,8),
            SCALE = convert(smallint, NULL),
            PSEUDO_COLUMN = convert(smallint,1)
        FROM
            systypes t, syscolumns c, master.dbo.spt_datatype_info d
        WHERE
            c.id = @table_id
            AND c.type = d.ss_dtype
            AND c.usertype = 80 /*    TIMESTAMP */
            AND t.usertype = 80 /*    TIMESTAMP */
        RETURN
    END

    /* ROWID, now find the id of the 'best' index for this table */

    IF @nullable = 'O'    /* Don't include any indexes that contain
                           nullable columns. */

        SELECT @indid = MIN(indid)
            FROM sysindexes i,syscolumns c,syscolumns c2
            WHERE
                i.status&2 = 2        /*    If Unique Index */
                AND c.id = i.id
                AND c2.id = c.id
                AND c2.colid < i.keycnt + (i.status&16)/16
                AND i.id = @table_id
                AND indid > 0        /*    Eliminate Table Row */
                AND c.name = index_col(@table_name,i.indid,c2.colid)
                GROUP BY indid HAVING SUM(c.status&8) = 0

    ELSE    /* Include indexes that are partially nullable. */

        SELECT @indid = MIN(indid)
            FROM sysindexes i
            WHERE
                status&2 = 2        /*    If Unique Index */
                AND id = @table_id
                AND indid > 0        /*    Eliminate Table Row */

    SELECT
        SCOPE = @scopeout,
        COLUMN_NAME = convert(varchar(32),INDEX_COL(@full_table_name,indid,c2.colid)),
        d.DATA_TYPE,
        case
            when t.usertype > 100 or t.usertype in (18,80) then convert(varchar(30),t.name)
            else d.TYPE_NAME
        end TYPE_NAME,
        case
            when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
            else isnull(convert(int,c.prec), 2147483647)
        end "PRECISION",
        case
            when d.ss_dtype IN (106, 108, 55, 63) then    /* decimal/numeric types */
                convert(int,c.prec+2)
            else
                isnull(d.length, convert(int,c.length))
        end LENGTH,
        SCALE = convert(smallint, c.scale),
        PSEUDO_COLUMN = convert(smallint,1)
    FROM
        sysindexes x,
        syscolumns c,
        master.dbo.spt_datatype_info d,
        systypes t,
        syscolumns c2    /* Self-join to generate list of index columns and */
                        /* to extract datatype names */
    WHERE
        x.id = @table_id
        AND c.name = INDEX_COL(@full_table_name,@indid,c2.colid)
        AND c.id = x.id
        AND c2.id = x.id
        AND c2.colid < x.keycnt+(x.status&16)/16
        AND x.indid = @indid
        AND t.type = d.ss_dtype
        AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
        AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128
        AND c.usertype = t.usertype
go

grant execute on sp_special_columns to public
go

dump tran master with no_log
go

print 'creating sp_sproc_columns'
go

/*    Procedure for pre-6.0 server */
CREATE PROCEDURE sp_sproc_columns (
                 @procedure_name        varchar(96) = '%',
                 @procedure_owner        varchar(90) = null,
                 @procedure_qualifier    varchar(32) = null,
                 @column_name            varchar(90) = null,
                 @ODBCVer                int = 2)
AS
    DECLARE @group_num int
    DECLARE @semi_position int
    DECLARE @full_procedure_name    char(187)
    DECLARE @procedure_id int

    if @column_name is null /*    If column name not supplied, match all */
        select @column_name = '%'
    if @procedure_qualifier is not null
    begin
        if db_name() <> @procedure_qualifier
        begin
            if @procedure_qualifier = ''
            begin
                /* in this case, we need to return an empty result set */
                /* because the user has requested a database with an empty name */
                select @procedure_name = ''
                select @procedure_owner = ''
            end
            else
            begin    /* If qualifier doesn't match current database */
                raiserror 20001 'Procedure qualifier must be name of current database'
                return
            end
        end
    end

    if @procedure_name is null
    begin    /*    If procedure name not supplied, match all */
        select @procedure_name = '%'
    end

    /* first we need to extract the procedure group number, if one exists */
    select @semi_position = charindex(';',@procedure_name)
    if (@semi_position > 0)
    begin    /* If group number separator (;) found */
        select @group_num = convert(int,substring(@procedure_name, @semi_position + 1, 2))
        select @procedure_name = substring(@procedure_name, 1, @semi_position -1)
    end
    else
    begin    /* No group separator, so default to group number of 1 */
        select @group_num = 1
    end

    if @procedure_owner is null
    begin    /* If unqualified procedure name */
        SELECT @full_procedure_name = @procedure_name
    end
    else
    begin    /* Qualified procedure name */
        SELECT @full_procedure_name = @procedure_owner + '.' + @procedure_name
    end

    /*    Get Object ID */
    SELECT @procedure_id = object_id(@full_procedure_name)
    if ((charindex('%',@full_procedure_name) = 0) and
        (charindex('_',@full_procedure_name) = 0)  and
        @procedure_id <> 0)
    begin
        /* this block is for the case where there is no pattern
            matching required for the procedure name */
        SELECT
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ ltrim(str(c.number,5))),
            COLUMN_NAME = convert(varchar(32),c.name),
            COLUMN_TYPE = convert(smallint, 0),
            d.DATA_TYPE,
            TYPE_NAME = t.name,
            "PRECISION" = isnull(d.data_precision, convert(int,c.length)),
            LENGTH = isnull(d.length, convert(int,c.length)),
            SCALE = d.numeric_scale,
            d.RADIX,
            d.NULLABLE,
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),null),
            d.SQL_DATETIME_SUB,
            CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',d.NULLABLE*3+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.id = @procedure_id
            AND c.id = o.id
            AND t.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND c.usertype = t.usertype
            AND c.name like @column_name
            AND c.number = @group_num
        UNION ALL
        SELECT           /* return value row*/
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ isnull(ltrim(str(c.number,5)),'1')),
            COLUMN_NAME = convert(varchar(32),'RETURN_VALUE'),
            COLUMN_TYPE = convert(smallint, 5),
            DATA_TYPE = convert(smallint, 4),
            TYPE_NAME = convert(varchar(30),'int'),
            "PRECISION" = convert(int,10),
            LENGTH = convert(int,4),
            SCALE = convert(smallint,0),
            RADIX = convert(smallint,10),
            NULLABLE = convert(smallint,0),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),NULL),
            SQL_DATETIME_SUB = convert(smallint,null),
            CHAR_OCTET_LENGTH = convert(int,null),
            ORDINAL_POSITION = convert(int,0),
            IS_NULLABLE = convert(varchar(254),'NO'),
            SS_DATA_TYPE = convert(tinyint,56)
        FROM
            syscolumns c,
            sysobjects o
        WHERE
            o.id = @procedure_id
            AND c.id =* o.id
            AND c.colid = 1
            AND 'RETURN_VALUE' like @column_name
        ORDER BY 1, 2, 3, 17
    end
    else
    begin
        /* this block is for the case where there IS pattern
            matching done on the procedure name */
        if @procedure_owner is null
            select @procedure_owner = '%'
        SELECT
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ ltrim(str(c.number,5))),
            COLUMN_NAME = convert(varchar(32),c.name),
            COLUMN_TYPE = convert(smallint, 0),
            d.DATA_TYPE,
            TYPE_NAME = t.name,
            "PRECISION" = isnull(d.data_precision, convert(int,c.length)),
            LENGTH = isnull(d.length, convert(int,c.length)),
            SCALE = d.numeric_scale,
            d.RADIX,
            d.NULLABLE,
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),null),
            d.SQL_DATETIME_SUB,
            CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',d.NULLABLE*3+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.name like @procedure_name
            AND user_name(o.uid) like @procedure_owner
            AND o.id = c.id
            AND t.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND c.usertype = t.usertype
            AND o.type = 'P'                            /* Just Procedures */
            AND c.name like @column_name
        UNION ALL
        SELECT           /* return value row*/
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ isnull(ltrim(str(c.number,5)),'1')),
            COLUMN_NAME = convert(varchar(32),'RETURN_VALUE'),
            COLUMN_TYPE = convert(smallint, 5),
            DATA_TYPE = convert(smallint, 4),
            TYPE_NAME = convert(varchar(30),'int'),
            "PRECISION" = convert(int,10),
            LENGTH = convert(int,4),
            SCALE = convert(smallint,0),
            RADIX = convert(smallint,10),
            NULLABLE = convert(smallint,0),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),NULL),
            SQL_DATETIME_SUB = convert(smallint,null),
            CHAR_OCTET_LENGTH = convert(int,null),
            ORDINAL_POSITION = convert(int,0),
            IS_NULLABLE = convert(varchar(254),'NO'),
            SS_DATA_TYPE = convert(tinyint,56)
        FROM
            syscolumns c,
            sysobjects o
        WHERE
            o.name like @procedure_name
            AND user_name(o.uid) like @procedure_owner
            AND c.id =* o.id
            AND c.colid = 1
            AND o.type = 'P'                        /* Just Procedures */
            AND 'RETURN_VALUE' like @column_name
        ORDER BY 1, 2, 3, 17
    end
go

if    (charindex('6.00', @@version) = 0 and
     charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.0 SQL Server.'
    print 'Ignore the following error.'
end
else
    drop proc sp_sproc_columns
go

/*    Procedure for 6.0 server */
CREATE PROCEDURE sp_sproc_columns (
                 @procedure_name        varchar(96) = '%',
                 @procedure_owner        varchar(90) = null,
                 @procedure_qualifier    varchar(32) = null,
                 @column_name            varchar(90) = null,
                 @ODBCVer                int = 2)
AS
    DECLARE @group_num int
    DECLARE @semi_position int
    DECLARE @full_procedure_name    char(187)
    DECLARE @procedure_id int

    if @column_name is null /*    If column name not supplied, match all */
        select @column_name = '%'
    if @procedure_qualifier is not null
    begin
        if db_name() <> @procedure_qualifier
        begin
            if @procedure_qualifier = ''
            begin
                /* in this case, we need to return an empty result set */
                /* because the user has requested a database with an empty name */
                select @procedure_name = ''
                select @procedure_owner = ''
            end
            else
            begin    /* If qualifier doesn't match current database */
                raiserror (15250, -1,-1,'Procedure')
                return
            end
        end
    end

    if @procedure_name is null
    begin    /*    If procedure name not supplied, match all */
        select @procedure_name = '%'
    end

    /* first we need to extract the procedure group number, if one exists */
    select @semi_position = charindex(';',@procedure_name)
    if (@semi_position > 0)
    begin    /* If group number separator (;) found */
        select @group_num = convert(int,substring(@procedure_name, @semi_position + 1, 2))
        select @procedure_name = substring(@procedure_name, 1, @semi_position -1)
    end
    else
    begin    /* No group separator, so default to group number of 1 */
        select @group_num = 1
    end

    if @procedure_owner is null
    begin    /* If unqualified procedure name */
        SELECT @full_procedure_name = @procedure_name
    end
    else
    begin    /* Qualified procedure name */
        SELECT @full_procedure_name = @procedure_owner + '.' + @procedure_name
    end

    /*    Get Object ID */
    SELECT @procedure_id = object_id(@full_procedure_name)
    if ((charindex('%',@full_procedure_name) = 0) and
        (charindex('_',@full_procedure_name) = 0)  and
        @procedure_id <> 0)
    begin
        /* this block is for the case where there is no pattern
            matching required for the procedure name */
        SELECT
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ ltrim(str(c.number,5))),
            COLUMN_NAME = convert(varchar(32),c.name),
            COLUMN_TYPE = convert(smallint, 1+((c.status/64)&1)),
            d.DATA_TYPE,
            TYPE_NAME = t.name,
            case
                when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
                else isnull(convert(int,c.prec), 2147483647)
            end "PRECISION",
            case
                when d.ss_dtype IN (106, 108, 55, 63) then    /* decimal/numeric types */
                    convert(int,c.prec+2)
                else
                    isnull(d.length, convert(int,c.length))
            end LENGTH,
            SCALE = convert(smallint, c.scale),
            d.RADIX,
            d.NULLABLE,
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),NULL),
            d.SQL_DATETIME_SUB,
            CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647)+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',d.NULLABLE*3+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.id = @procedure_id
            AND c.id = o.id
            AND c.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND isnull(d.AUTO_INCREMENT,0) = 0
            AND c.usertype *= t.usertype
            AND c.name like @column_name
            AND c.number = @group_num
        UNION ALL
        SELECT           /* return value row*/
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ isnull(ltrim(str(c.number,5)),'1')),
            COLUMN_NAME = convert(varchar(32),'RETURN_VALUE'),
            COLUMN_TYPE = convert(smallint, 5),
            DATA_TYPE = convert(smallint, 4),
            TYPE_NAME = convert(varchar(30),'int'),
            "PRECISION" = convert(int,10),
            LENGTH = convert(int,4),
            SCALE = convert(smallint,0),
            RADIX = convert(smallint,10),
            NULLABLE = convert(smallint,0),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),NULL),
            SQL_DATETIME_SUB = convert(smallint,null),
            CHAR_OCTET_LENGTH = convert(int,null),
            ORDINAL_POSITION = convert(int,0),
            IS_NULLABLE = convert(varchar(254),'NO'),
            SS_DATA_TYPE = convert(tinyint,56)
        FROM
            syscolumns c,
            sysobjects o
        WHERE
            o.id = @procedure_id
            AND c.id =* o.id
            AND c.colid = 1
            AND 'RETURN_VALUE' like @column_name
        ORDER BY 1, 2, 3, 17
    end
    else
    begin
        /* this block is for the case where there IS pattern
            matching done on the procedure name */
        if @procedure_owner is null
            select @procedure_owner = '%'
        SELECT
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ ltrim(str(c.number,5))),
            COLUMN_NAME = convert(varchar(32),c.name),
            COLUMN_TYPE = convert(smallint, 1+((c.status/64)&1)),
            d.DATA_TYPE,
            TYPE_NAME = t.name,
            case
                when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
                else isnull(convert(int,c.prec), 2147483647)
            end "PRECISION",
            case
                when d.ss_dtype IN (106, 108, 55, 63) then    /* decimal/numeric types */
                    convert(int,c.prec+2)
                else
                    isnull(d.length, convert(int,c.length))
            end LENGTH,
            SCALE = convert(smallint, c.scale),
            d.RADIX,
            d.NULLABLE,
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),NULL),
            d.SQL_DATETIME_SUB,
            CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647)+d.charbin,
            ORDINAL_POSITION = convert(int,c.colid),
            IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',d.NULLABLE*3+1,3))),
            SS_DATA_TYPE = c.type
        FROM
            syscolumns c,
            sysobjects o,
            master.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.name like @procedure_name
            AND user_name(o.uid) like @procedure_owner
            AND o.id = c.id
            AND c.type = d.ss_dtype
            AND c.length = isnull(d.fixlen, c.length)
            AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
            AND isnull(d.AUTO_INCREMENT,0) = 0
            AND c.usertype *= t.usertype
            AND o.type = 'P'                            /* Just Procedures */
            AND c.name like @column_name
        UNION ALL
        SELECT           /* return value row*/
            PROCEDURE_QUALIFIER = convert(varchar(32),DB_NAME()),
            PROCEDURE_OWNER = convert(varchar(32),USER_NAME(o.uid)),
            PROCEDURE_NAME = convert(varchar(41),o.name +';'+ isnull(ltrim(str(c.number,5)),'1')),
            COLUMN_NAME = convert(varchar(32),'RETURN_VALUE'),
            COLUMN_TYPE = convert(smallint, 5),
            DATA_TYPE = convert(smallint, 4),
            TYPE_NAME = convert(varchar(30),'int'),
            "PRECISION" = convert(int,10),
            LENGTH = convert(int,4),
            SCALE = convert(smallint,0),
            RADIX = convert(smallint,10),
            NULLABLE = convert(smallint,0),
            REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
            COLUMN_DEF = convert(varchar(254),NULL),
            SQL_DATETIME_SUB = convert(smallint,null),
            CHAR_OCTET_LENGTH = convert(int,null),
            ORDINAL_POSITION = convert(int,0),
            IS_NULLABLE = convert(varchar(254),'NO'),
            SS_DATA_TYPE = convert(tinyint,56)
        FROM
            syscolumns c,
            sysobjects o
        WHERE
            o.name like @procedure_name
            AND user_name(o.uid) like @procedure_owner
            AND c.id =* o.id
            AND c.colid = 1
            AND o.type = 'P'                        /* Just Procedures */
            AND 'RETURN_VALUE' like @column_name
        ORDER BY 1, 2, 3, 17
    end
go

grant execute on sp_sproc_columns to public
go

dump tran master with no_log
go

print 'creating sp_statistics'
go

CREATE PROCEDURE sp_statistics (
                 @table_name        varchar(32),
                 @table_owner        varchar(32) = null,
                 @table_qualifier    varchar(32) = null,
                 @index_name        varchar(32) = '%',
                 @is_unique         char(1) = 'N',
                 @accuracy            char(1) = 'Q')
AS
    DECLARE @indid                int
    DECLARE @lastindid            int
    DECLARE @table_id            int
    DECLARE @full_table_name    char(70)

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end

    if @accuracy not in ('Q','E')
        begin
            raiserror 20002 'Illegal ''accuracy'' specified -- must be ''Q'' or ''E''.'
            return
        end

    if (@@trancount <> 0 and charindex('6.50', @@version) = 0)
    begin    /* If inside a transaction */
        raiserror 20003 'The procedure ''sp_statistics'' cannot be executed from within a transaction.'
        return
    end
    create table #TmpIndex(
        TABLE_QUALIFIER varchar(32) NULL,
        TABLE_OWNER     varchar(32) NULL,
        TABLE_NAME        varchar(32) NOT NULL,
        INDEX_QUALIFIER varchar(32) null,
        INDEX_NAME        varchar(32) null,
        NON_UNIQUE        smallint null,
        TYPE            smallint NOT NULL,
        SEQ_IN_INDEX    smallint null,
        COLUMN_NAME     varchar(32) null,
        COLLATION        char(1) null,
        index_id        int null,
        CARDINALITY     int null,
        PAGES            int null,
        status            smallint NOT NULL)
    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = @table_name
    end
    else
    begin    /* Qualified table name */
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)

    /*    Start at lowest index id */
    SELECT @indid = min(indid)
    FROM sysindexes
    WHERE id = @table_id
        AND indid > 0
        AND indid < 255

    WHILE @indid <> NULL
    BEGIN
        INSERT #TmpIndex    /* Add all columns that are in index */
            SELECT
                DB_NAME(),                                /* TABLE_QUALIFIER */
                USER_NAME(o.uid),                        /* TABLE_OWNER       */
                o.name,                                 /* TABLE_NAME       */
                o.name,                                 /* INDEX_QUALIFIER */
                x.name,                                 /* INDEX_NAME       */
                0,                                        /* NON_UNIQUE       */
                1,                                        /* SQL_INDEX_CLUSTERED */
                colid,                                    /* SEQ_IN_INDEX    */
                INDEX_COL(@full_table_name,indid,colid),/* COLUMN_NAME       */
                'A',                                    /* COLLATION       */
                @indid,                                 /* index_id        */
                x.rows,                                 /* CARDINALITY       */
                x.dpages,                                /* PAGES           */
                x.status                                /* status            */
            FROM sysindexes x, syscolumns c, sysobjects o
            WHERE
                x.id = @table_id
                AND x.id = o.id
                AND x.id = c.id
                AND c.colid < keycnt+(x.status&16)/16
                AND x.indid = @indid
        /*
        **      Now move @indid to the next index.
        */
        SELECT @lastindid = @indid
        SELECT @indid = NULL

        SELECT @indid = min(indid)
        FROM sysindexes
        WHERE id = @table_id
            AND indid > @lastindid
            AND indid < 255
    END

    UPDATE #TmpIndex
        SET NON_UNIQUE = 1
        WHERE status&2 <> 2 /* If non-unique index */
    UPDATE #TmpIndex
        SET
            TYPE = 3,            /* SQL_INDEX_OTHER */
            CARDINALITY = NULL,
            PAGES = NULL
        WHERE index_id > 1    /* If non-clustered index */

    /* now add row for table statistics */
    INSERT #TmpIndex
        SELECT
            DB_NAME(),                /* TABLE_QUALIFIER */
            USER_NAME(o.uid),        /* TABLE_OWNER       */
            o.name,                 /* TABLE_NAME       */
            null,                    /* INDEX_QUALIFIER */
            null,                    /* INDEX_NAME       */
            null,                    /* NON_UNIQUE       */
            0,                        /* SQL_TABLE_STAT  */
            null,                    /* SEQ_IN_INDEX    */
            null,                    /* COLUMN_NAME       */
            null,                    /* COLLATION       */
            0,                        /* index_id        */
            x.rows,                 /* CARDINALITY       */
            x.dpages,                /* PAGES           */
            0                        /* status           */
        FROM sysindexes x, sysobjects o
        WHERE o.id = @table_id
            AND x.id = o.id
            AND (x.indid = 0 or x.indid = 1)    /*    If there are no indexes */
                                                /*    then table stats are in */
                                                /*    a row with indid =0        */

    if @is_unique <> 'Y'    /* If all indexes desired */
        SELECT
            TABLE_QUALIFIER,
            TABLE_OWNER,
            TABLE_NAME,
            NON_UNIQUE,
            INDEX_QUALIFIER,
            INDEX_NAME,
            TYPE,
            SEQ_IN_INDEX,
            COLUMN_NAME,
            COLLATION,
            CARDINALITY,
            PAGES,
            FILTER_CONDITION = convert(varchar(128),null)
        FROM #TmpIndex
        WHERE
            INDEX_NAME like @index_name /* If matching name */
            or INDEX_NAME is null        /* If SQL_TABLE_STAT row */
        ORDER BY 4, 7, 6, 8
    else                    /* If only unique indexes desired */
        SELECT
            TABLE_QUALIFIER,
            TABLE_OWNER,
            TABLE_NAME,
            NON_UNIQUE,
            INDEX_QUALIFIER,
            INDEX_NAME,
            TYPE,
            SEQ_IN_INDEX,
            COLUMN_NAME,
            COLLATION,
            CARDINALITY,
            PAGES,
            FILTER_CONDITION = convert(varchar(128),null)
        FROM #TmpIndex
        WHERE
            (NON_UNIQUE = 0             /* If unique */
                or NON_UNIQUE is NULL)    /* If SQL_TABLE_STAT row */
            and (INDEX_NAME like @index_name    /* If matching name */
                or INDEX_NAME is null)    /* If SQL_TABLE_STAT row */
        ORDER BY 4, 7, 6, 8

    DROP TABLE #TmpIndex
go

grant execute on sp_statistics to public
go

dump tran master with no_log
go

print 'creating sp_stored_procedures'
go

create procedure sp_stored_procedures(
                        @sp_name        varchar(96) = null,
                        @sp_owner        varchar(90) = null,
                        @sp_qualifier    varchar(32) = null)
as
    declare @proc_type smallint

    if @sp_qualifier is not null
    begin
        if db_name() <> @sp_qualifier
        begin
            if @sp_qualifier = ''
            begin
                /* in this case, we need to return an empty result set */
                /* because the user has requested a database with an empty name */
                select @sp_name = ''
                select @sp_owner = ''
            end else
            begin    /* If qualifier doesn't match current database */
                raiserror 20001 'Procedure qualifier must be name of current database'
                return
            end
        end
    end

    if @sp_name is null
    begin  /*  If procedure name not supplied, match all */
        select @sp_name = '%'
    end
    else begin
        if (@sp_owner is null) and (charindex('%', @sp_name) = 0)
        begin
            if exists (select * from sysobjects
                where uid = user_id()
                    and name = @sp_name
                    and type = 'P') /* Object type of Procedure */
            begin
                select @sp_owner = user_name()
            end
        end
    end
    if @sp_owner is null    /*    If procedure owner not supplied, match all */
        select @sp_owner = '%'

    select @proc_type=2        /* Return 2 for 4.2 and later servers. */

    select
        PROCEDURE_QUALIFIER = convert(varchar(32),db_name()),
        PROCEDURE_OWNER = convert(varchar(32),user_name(o.uid)),
        PROCEDURE_NAME = convert(varchar(41),o.name +';'+ ltrim(str(p.number,5))),
        NUM_INPUT_PARAMS = -1,    /* Constant since value unknown */
        NUM_OUTPUT_PARAMS = -1, /* Constant since value unknown */
        NUM_RESULT_SETS = -1,    /* Constant since value unknown */
        REMARKS = convert(varchar(254),null),    /* Remarks are NULL */
        PROCEDURE_TYPE = @proc_type
    from
        sysobjects o,sysprocedures p,sysusers u
    where
        o.name like @sp_name
        and p.sequence = 0
        and user_name(o.uid) like @sp_owner
        and o.type = 'P'        /* Object type of Procedure */
        and p.id = o.id
        and u.uid = user_id()    /* constrain sysusers uid for use in subquery */
        and (suser_id() = 1     /* User is the System Administrator */
            or o.uid = user_id()    /* User created the object */
            /* here's the magic... select the highest precedence of permissions in the order (user,group,public)  */
            or ((select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
             from sysprotects p
             /* outer join to correlate with all rows in sysobjects */
             where p.id =* o.id
                 /*  get rows for public,current user,user's group */
                 and (p.uid = 0 or p.uid = user_id() or p.uid =* u.gid)
                 /* check for SELECT,EXECUTE privilege */
                 and (action in (193,224)))&1    /* more magic...normalize GRANT */
            ) = 1     /* final magic...compare Grants    */
        )
    order by 1, 2, 3
go
grant execute on sp_stored_procedures to public
go

dump tran master with no_log
go


print 'creating sp_table_privileges'
go

/*    Procedure for pre 6.50 server */
CREATE PROCEDURE sp_table_privileges (
            @table_name         varchar(90),
            @table_owner        varchar(90) = null,
            @table_qualifier    varchar(32) = null)
as

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end
    if (@@trancount <> 0)
    begin    /* If inside a transaction */
        raiserror 20003 'The procedure ''sp_table_privileges'' cannot be executed from within a transaction.'
        return
    end
    if @table_name is null
        select @table_name = '%'
    if @table_owner is null /* If no owner supplied, force wildcard */
        select @table_owner = '%'

    create table #table_priv1(
        id                        int NOT NULL,
        grantor                 smallint NOT NULL,
        grantee                 smallint NOT NULL,
        select_privilege        bit,
        insert_privilege        bit,
        update_privilege        bit,
        delete_privilege        bit,
        references_privilege    bit,
        select_privilege_grant    bit,
        insert_privilege_grant    bit,
        update_privilege_grant    bit,
        delete_privilege_grant    bit,
        references_privilege_grant    bit,
        uid                     smallint NOT NULL,
        gid                     smallint NOT NULL)

    insert into #table_priv1
        select distinct
            o.id,
            o.uid,
            u.uid,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            u.uid,
            u.gid
        from sysusers u, sysobjects o, sysprotects p
        where
            o.name like @table_name
            and user_name(o.uid) like @table_owner
            and u.uid <> u.gid
            and u.uid *= p.uid and o.id *= p.id
            and p.protecttype in (204, 205)
            and sysstat & 0xf in (1,2,3)    /* only valid for system tables,
                                            ** user tables, and views. */

    /*
    ** now add/update row for table owner
    */
    if exists (
        select *
            from #table_priv1
            where grantor = grantee)
    begin
        update #table_priv1
        set
            select_privilege_grant = 1,
            update_privilege_grant = 1,
            insert_privilege_grant = 1,
            delete_privilege_grant = 1,
            references_privilege_grant = 1,
            grantor = (select uid from sysusers where suid = 1)
        where grantor = grantee
    end
    else
    begin
        insert into #table_priv1
            select
                o.id,
                u1.uid,
                o.uid,
                0,
                0,
                0,
                0,
                0,
                1,
                1,
                1,
                1,
                1,
                o.uid,
                u.gid
            from sysobjects o, sysusers u, sysusers u1
            where o.name like @table_name
            and user_name(o.uid) like @table_owner
            and u.uid = o.uid
            and sysstat & 0xf in (1,2,3)    /* only valid for system tables,
                                           ** user tables, and views. */
            and u1.suid = 1        /* grantor is dbo of database */

    end

    update #table_priv1
    set select_privilege = 1
        from sysprotects p
    where
        #table_priv1.id = p.id
        and (#table_priv1.uid = p.uid
            or #table_priv1.gid = p.uid
            or p.uid = 0)
        and protecttype = 205
        and action = 193
        and not exists (
            select * from sysprotects
            where
                #table_priv1.id = sysprotects.id
                and (#table_priv1.uid = uid
                    or #table_priv1.gid = uid
                    or uid = 0)
                and protecttype = 206
                and action = 193)

    update #table_priv1
    set insert_privilege = 1
        from sysprotects p
    where
        #table_priv1.id = p.id
        and (#table_priv1.uid = p.uid
            or #table_priv1.gid = p.uid
            or p.uid = 0)
        and protecttype = 205
        and action = 195
        and not exists (
            select * from sysprotects
            where
                #table_priv1.id = sysprotects.id
                and (#table_priv1.uid = uid
                    or #table_priv1.gid = uid
                    or uid = 0)
                and protecttype = 206
                and action = 195)

    update #table_priv1
    set delete_privilege = 1
        from sysprotects p
    where
        exists (
            select * from sysprotects
            where
                #table_priv1.id = sysprotects.id
                and (#table_priv1.uid = uid
                    or #table_priv1.gid = uid
                    or uid = 0)
                and protecttype = 205
                and action = 196)
        and not exists (select * from sysprotects
            where
                #table_priv1.id = sysprotects.id
                and (#table_priv1.uid = uid
                    or #table_priv1.gid = uid
                    or uid = 0)
                and protecttype = 206
                and action = 196)

    update #table_priv1
    set update_privilege = 1
        from sysprotects p
    where
        #table_priv1.id = p.id
        and (#table_priv1.uid = p.uid
            or #table_priv1.gid = p.uid
            or p.uid = 0)
        and protecttype = 205
        and action = 197
        and not exists (
            select * from sysprotects
            where
                #table_priv1.id = sysprotects.id
                and (#table_priv1.uid = uid
                    or #table_priv1.gid = uid
                    or uid = 0)
                and protecttype = 206
                and action = 197)

    update #table_priv1
    set references_privilege = 1
        from sysprotects p
    where
        #table_priv1.id = p.id
        and (#table_priv1.uid = p.uid
            or #table_priv1.gid = p.uid
            or p.uid = 0)
        and protecttype = 205
        and action = 26
        and not exists (
            select * from sysprotects
            where
                #table_priv1.id = sysprotects.id
                and (#table_priv1.uid = uid
                    or #table_priv1.gid = uid
                    or uid = 0)
                and protecttype = 206
                and action = 26)

    create table #table_priv2(
        id                int NOT NULL,
        grantor         smallint NOT NULL,
        grantee         smallint NOT NULL,
        PRIVILEGE        varchar(32) NOT NULL,
        IS_GRANTABLE    varchar(3) NULL)

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'SELECT',
            'NO'
        from #table_priv1
        where select_privilege = 1 and select_privilege_grant = 0

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'INSERT',
            'NO'
        from #table_priv1
        where insert_privilege = 1 and insert_privilege_grant = 0

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'DELETE',
            'NO'
        from #table_priv1
        where delete_privilege = 1 and delete_privilege_grant = 0

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'UPDATE',
            'NO'
        from #table_priv1
        where update_privilege = 1 and update_privilege_grant = 0

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'REFERENCES',
            'NO'
        from #table_priv1
        where references_privilege = 1 and references_privilege_grant = 0

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'SELECT',
            'YES'
        from #table_priv1
        where select_privilege_grant = 1

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'INSERT',
            'YES'
        from #table_priv1
        where insert_privilege_grant = 1

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'DELETE',
            'YES'
        from #table_priv1
        where delete_privilege_grant = 1

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'UPDATE',
            'YES'
        from #table_priv1
        where update_privilege_grant = 1

    insert into #table_priv2
        select
            id,
            grantor,
            grantee,
            'REFERENCES',
            'YES'
        from #table_priv1
        where references_privilege_grant = 1


    select
        convert(varchar(32),db_name()) TABLE_QUALIFIER,
        convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
        convert(varchar(32),o.name) TABLE_NAME,
        convert(varchar(32),user_name(grantor)) GRANTOR,
        convert(varchar(32),user_name(grantee)) GRANTEE,
        PRIVILEGE,
        IS_GRANTABLE
    from #table_priv2 t, sysobjects o where o.id = t.id
    order by 2,3,6
go

if    (charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.50 SQL Server.'
    print 'Ignore the following errors.'
end
else
    drop proc sp_table_privileges
go

/*    Procedure for 6.50 server */
CREATE PROCEDURE sp_table_privileges (
            @table_name         varchar(90),
            @table_owner        varchar(90) = null,
            @table_qualifier    varchar(32) = null)
as

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror 20001 'Table qualifier must be name of current database'
            return
        end
    end
    if @table_name is null
        select @table_name = '%'
    if @table_owner is null /* If no owner supplied, force wildcard */
        select @table_owner = '%'

    select
        convert(varchar(32),db_name()) TABLE_QUALIFIER,
        convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
        convert(varchar(32),object_name(o.id)) TABLE_NAME,
        convert(varchar(32),user_name(p.grantor)) GRANTOR,
        convert(varchar(32),user_name(u.uid)) GRANTEE,
        case p.action
             when 193 then 'SELECT'
             when 195 then 'INSERT'
             when 196 then 'DELETE'
             when 197 then 'UPDATE'
             else convert(varchar(32),'REFERENCES')
        end PRIVILEGE,
        case when p.protecttype = 205 then 'NO'
            else 'YES'
        end IS_GRANTABLE
    from sysprotects p, sysobjects o, sysusers u
    where
        p.id = o.id
        and object_name(o.id) like @table_name
        and user_name(o.uid) like @table_owner
            /* expand groups */
        and ((p.uid = u.uid and u.uid <> u.gid) or
             (p.uid = u.gid and u.uid <> u.gid))
        and p.protecttype <> 206    /* only grant rows */
        and p.action in (26,193,195,196,197)
        and o.uid <> u.uid            /* no rows for owner */
        and not exists (            /* exclude revoke'd privileges */
            select *
            from sysprotects p1
            where
                p1.protecttype = 206
                and p1.action = p.action
                and p1.id = p.id
                and p1.uid = u.uid)
    union all
    select    /*    Add rows for table owner */
        convert(varchar(32),db_name()) TABLE_QUALIFIER,
        convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
        convert(varchar(32),object_name(o.id)) TABLE_NAME,
        convert(varchar(32),user_name(u.uid)) GRANTOR,
        convert(varchar(32),user_name(o.uid)) GRANTEE,
        case v.number
            when 193 then 'SELECT'
            when 195 then 'INSERT'
            when 196 then 'DELETE'
            when 197 then 'UPDATE'
            else convert(varchar(32),'REFERENCES')
        end PRIVILEGE,
        convert(varchar,'YES') IS_GRANTABLE
    from sysobjects o, spt_values v, sysusers u
    where
        object_name(o.id) like @table_name
        and user_name(o.uid) like @table_owner
        and u.suid = 1        /* grantor is dbo of database */
        and v.type = 'P'    /* cross product to get all exposed privileges */
        and v.number in (26,193,195,196,197)
        and not exists (    /* exclude revoke'd privileges */
            select *
            from sysprotects p1
            where
                p1.protecttype = 206
                and p1.action = v.number
                and p1.id = o.id
                and p1.uid = o.uid)
    order by 2,3,6
go

grant execute on sp_table_privileges to public
go

dump tran master with no_log
go

print 'creating sp_tables'
go

create procedure sp_tables(
               @table_name        varchar(90)    = null,
               @table_owner     varchar(90)    = null,
               @table_qualifier varchar(90)    = null,
               @table_type        varchar(100) = null)
as
    declare @type1 varchar(3)
    declare @tableindex int


    /* Special feature #1:    enumerate databases when owner and name
         are blank but qualifier is explicitly '%'.  */
    if @table_qualifier = '%' and
        @table_owner = '' and
        @table_name = ''
    begin    /* If enumerating databases */
        select
            TABLE_QUALIFIER = convert(varchar(32),d.name),
            TABLE_OWNER = convert(varchar(32),null),
            TABLE_NAME = convert(varchar(32),null),
            TABLE_TYPE = convert(varchar(32),null),
            REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
        from master..sysdatabases d
        where d.name <> 'model'    /* eliminate MODEL database */
        order by 1
    end

    /* Special feature #2:    enumerate owners when qualifier and name
         are blank but owner is explicitly '%'.  */
    else if @table_qualifier = '' and
        @table_owner = '%' and
        @table_name = ''
    begin    /* If enumerating owners */
        select distinct
            TABLE_QUALIFIER = convert(varchar(32),null),
            TABLE_OWNER = convert(varchar(32),user_name(uid)),
            TABLE_NAME = convert(varchar(32),null),
            TABLE_TYPE = convert(varchar(32),null),
            REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
        from sysobjects
        order by 2
    end

    /* Special feature #3:    enumerate table types when qualifier, owner and
         name are blank but table type is explicitly '%'.    */
    else if @table_qualifier = '' and
        @table_owner = '' and
        @table_name = '' and
        @table_type = '%'
    begin    /* If enumerating table types */
        select
            TABLE_QUALIFIER = convert(varchar(32),null),
            TABLE_OWNER = convert(varchar(32),null),
            TABLE_NAME = convert(varchar(32),null),
            TABLE_TYPE = convert(varchar(32),rtrim(substring('SYSTEM TABLETABLE       VIEW',(colid-1)*12+1,12))),
            REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
        from sysobjects o, syscolumns c
        where o.id=c.id and o.name='sysusers' and colid<=3
    end

    else
    begin /* end of special features -- do normal processing */
        if @table_qualifier is not null
        begin
            if db_name() <> @table_qualifier
            begin
                if @table_qualifier = ''
                begin  /* If empty qualifier supplied */
                    /* Force an empty result set */
                    select @table_name = ''
                    select @table_owner = ''
                end
                else
                begin    /* If qualifier doesn't match current database */
                    raiserror 20001 'Table qualifier must be name of current database'
                    return
                end
            end
        end
        if @table_type is null
        begin    /* Select all ODBC supported table types */
            select @type1 = 'SUV'
        end
        else
        begin
            /*    TableType is case sensitive if CS server */
            select @type1 = null
            if (charindex('''SYSTEM TABLE''',@table_type) <> 0)
                select @type1 = @type1 + 'S'    /* Add System Tables */
            if (charindex('''TABLE''',@table_type) <> 0)
                select @type1 = @type1 + 'U'    /* Add User Tables */
            if (charindex('''VIEW''',@table_type) <> 0)
                select @type1 = @type1 + 'V'    /* Add Views */
        end
        if @table_name is null
        begin    /*    If table name not supplied, match all */
            select @table_name = '%'
        end
        else
        begin
            if (@table_owner is null) and (charindex('%', @table_name) = 0)
            begin    /* If owner not specified and table is specified */
                if exists (select * from sysobjects
                    where uid = user_id()
                    and name = @table_name
                    and (type = 'U' or type = 'V' or type = 'S'))
                begin    /* Override supplied owner w/owner of table */
                    select @table_owner = user_name()
                end
            end
        end
        if @table_owner is null /* If no owner supplied, force wildcard */
            select @table_owner = '%'
        select
            TABLE_QUALIFIER = convert(varchar(32),db_name()),
            TABLE_OWNER = convert(varchar(32),user_name(o.uid)),
            TABLE_NAME = convert(varchar(32),o.name),    /* make nullable */
            TABLE_TYPE = convert(varchar(32),rtrim(
                substring('SYSTEM TABLE            TABLE       VIEW       ',
                    (ascii(o.type)-83)*12+1,12))),    /* 'S'=0,'U'=2,'V'=3 */
            REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
        from sysusers u, sysobjects o
        where
            o.name like @table_name
            and user_name(o.uid) like @table_owner
            and charindex(substring(o.type,1,1),@type1)! = 0 /* Only desired types */
            and u.uid = user_id() /* constrain sysusers uid for use in subquery */
            and (
                suser_id() = 1     /* User is the System Administrator */
                or o.uid = user_id()     /* User created the object */
                /* here's the magic... select the highest precedence of permissions in the order (user,group,public)  */
                or ((select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
                    from sysprotects p
                    /* outer join to correlate with all rows in sysobjects */
                    where p.id =* o.id
                        /* get rows for public,current user,user's group */
                        and (p.uid = 0 or p.uid = user_id() or p.uid =* u.gid)
                        /* check for SELECT,EXECUTE privilege */
                        and (action in (193,224)))&1     /* more magic...normalize GRANT */
                    ) = 1    /* final magic...compare Grants      */
            )
        order by 4, 1, 2, 3
    end
go

grant execute on sp_tables to public
go

dump tran master with no_log
go

if    (charindex('6.50', @@version) = 0)
begin
    print ''
    print ''
    print 'Warning:'
    print 'you are installing the stored procedures '
    print 'on a pre 6.50 SQL Server.'
    print 'Ignore the following errors.'
end

print 'creating sp_ddopen'
go

create procedure sp_ddopen(
               @handle            int output,
               @procname        sysname(30),
               @scrollopt        int output,
               @ccopt            int output,
               @rows            int output,
               @p1                varchar(255) = null,
               @p2                varchar(255) = null,
               @p3                varchar(255) = null,
               @p4                varchar(255) = null,
               @p5                varchar(255) = null,
               @p6                varchar(255) = null,
               @p7                int = null,
               @ODBCVer         int = 2)
as
    declare @ret int

    if @procname = 'sp_column_privileges'
    begin
        create table #spcolpriv (
            TABLE_QUALIFIER varchar(32) null,
            TABLE_OWNER varchar(32) null,
            TABLE_NAME varchar(32) not null,
            COLUMN_NAME varchar(32) not null,
            GRANTOR varchar(32) null,
            GRANTEE varchar(32) not null,
            PRIVILEGE varchar(32) not null,
            IS_GRANTABLE varchar(32) null
            )
        insert into #spcolpriv exec 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
    end
    else if @procname = 'sp_columns'
    begin
        create table #spcolumns (
            TABLE_QUALIFIER varchar(32) null,
            TABLE_OWNER varchar(32) null,
            TABLE_NAME varchar(32) not null,
            COLUMN_NAME varchar(32) not null,
            DATA_TYPE smallint not null,
            TYPE_NAME varchar(30) not null,
            "PRECISION" int null,
            LENGTH int null,
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) null,
            COLUMN_DEF varchar(254) null,
            DATETIME_CODE int null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) null,
            SS_DATA_TYPE tinyint null
            )
        insert into #spcolumns exec 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_datatype_info'
    begin
        create table #spdatatypeinfo (
            TYPE_NAME            varchar(32)  not null,
            DATA_TYPE            smallint not null,
            "PRECISION"            int null,
            LITERAL_PREFIX        varchar(32)    null,
            LITERAL_SUFFIX        varchar(32)    null,
            CREATE_PARAMS        varchar(32)    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     varchar(128) 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,
            USERTYPE            tinyint not null)
        insert into #spdatatypeinfo exec sp_datatype_info @p7,@ODBCVer
        exec @ret = sp_cursoropen @handle output,
            'select * from #spdatatypeinfo',
            @scrollopt output, @ccopt output, @rows output
        drop table #spdatatypeinfo
    end
    else if @procname = 'sp_fkeys'
    begin
        create table #spfkeys (
            PKTABLE_QUALIFIER varchar(32)     null,
            PKTABLE_OWNER varchar(32)    null,
            PKTABLE_NAME varchar(32)  not null,
            PKCOLUMN_NAME varchar(32)  not null,
            FKTABLE_QUALIFIER varchar(32)    null,
            FKTABLE_OWNER varchar(32)    null,
            FKTABLE_NAME varchar(32)  not null,
            FKCOLUMN_NAME varchar(32)  not null,
            KEY_SEQ smallint not null,
            UPDATE_RULE smallint null,
            DELETE_RULE smallint null,
            FK_NAME varchar(32) null,
            PK_NAME varchar(32) null
            )
        insert into #spfkeys exec 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
    end
    else if @procname = 'sp_pkeys'
    begin
        create table #sppkeys (
            TABLE_QUALIFIER varchar(32)   null,
            TABLE_OWNER varchar(32)   null,
            TABLE_NAME varchar(32)    not null,
            COLUMN_NAME varchar(32)  not null,
            KEY_SEQ smallint not null,
            PK_NAME varchar(32) null
            )
        insert into #sppkeys exec sp_pkeys @p1,@p2,@p3
        exec @ret = sp_cursoropen @handle output,
            'select * from #sppkeys',
            @scrollopt output, @ccopt output, @rows output
        drop table #sppkeys
    end
    else if @procname = 'sp_special_columns'
    begin
        create table #spspeccol (
            SCOPE smallint null,
            COLUMN_NAME varchar(32) not null,
            DATA_TYPE smallint not null,
            TYPE_NAME varchar(30) not null,
            "PRECISION" int null,
            LENGTH int null,
            SCALE smallint null,
            PSEUDO_COLUMN smallint null
            )
        insert into #spspeccol exec sp_special_columns @p1,@p2,@p3,@p4,@p5,@p6,@ODBCVer
        exec @ret = sp_cursoropen @handle output,
            'select * from #spspeccol',
            @scrollopt output, @ccopt output, @rows output
        drop table #spspeccol
    end
    else if @procname = 'sp_sproc_columns'
    begin
        create table #spproccol (
            PROCEDURE_QUALIFIER varchar(32)  null,
            PROCEDURE_OWNER varchar(32)  null,
            PROCEDURE_NAME varchar(32) not null,
            COLUMN_NAME varchar(32) not null,
            COLUMN_TYPE smallint not null,
            DATA_TYPE smallint not null,
            TYPE_NAME varchar(30) not null,
            "PRECISION" int null,
            LENGTH int null,
            SCALE smallint null,
            RADIX smallint null,
            NULLABLE smallint not null,
            REMARKS varchar(254) null,
            COLUMN_DEF varchar(254) null,
            SQL_DATETIME_SUB smallint null,
            CHAR_OCTET_LENGTH int null,
            ORDINAL_POSITION int not null,
            IS_NULLABLE varchar(254) null,
            SS_DATA_TYPE tinyint null
            )
        insert into #spproccol exec 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_statistics'
    begin
        create table #spstatistics (
            TABLE_QUALIFIER varchar(32)   null,
            TABLE_OWNER varchar(32)   null,
            TABLE_NAME varchar(32)    not null,
            NON_UNIQUE smallint null,
            INDEX_QUALIFIER varchar(32) null,
            INDEX_NAME varchar(32) null,
            TYPE smallint not null,
            SEQ_IN_INDEX smallint null,
            COLUMN_NAME varchar(32) null,
            COLLATION char(1) null,
            CARDINALITY int null,
            PAGES int null,
            FILTER_CONDITION varchar(128) null
            )
        insert into #spstatistics exec 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
    end
    else if @procname = 'sp_stored_procedures'
    begin
        create table #spprocedures (
            PROCEDURE_QUALIFIER varchar(32)  null,
            PROCEDURE_OWNER varchar(32)  null,
            PROCEDURE_NAME varchar(32) not null,
            NUM_INPUT_PARAMS int null,
            NUM_OUTPUT_PARAMS int null,
            NUM_RESULT_SETS int null,
            REMARKS varchar(254) null,
            PROCEDURE_TYPE smallint null
            )
        insert into #spprocedures exec sp_stored_procedures @p1,@p2,@p3
        exec @ret = sp_cursoropen @handle output,
            'select * from #spprocedures',
            @scrollopt output, @ccopt output, @rows output
        drop table #spprocedures
    end
    else if @procname = 'sp_table_privileges'
    begin
        create table #sptabpriv (
            TABLE_QUALIFIER varchar(32) null,
            TABLE_OWNER varchar(32) null,
            TABLE_NAME varchar(32) not null,
            GRANTOR varchar(32) null,
            GRANTEE varchar(32) not null,
            PRIVILEGE varchar(32) not null,
            IS_GRANTABLE varchar(32) null
            )
        insert into #sptabpriv exec sp_table_privileges @p1,@p2,@p3
        exec @ret = sp_cursoropen @handle output,
            'select * from #sptabpriv',
            @scrollopt output, @ccopt output, @rows output
        drop table #sptabpriv
    end
    else if @procname = 'sp_tables'
    begin
        create table #sptables (
            TABLE_QUALIFIER varchar(32) not null,
            TABLE_OWNER varchar(32) null,
            TABLE_NAME varchar(32) null,
            TABLE_TYPE    varchar(32) null,
            REMARKS varchar(254) null)
        insert into #sptables exec sp_tables @p1,@p2,@p3,@p4
        exec @ret = sp_cursoropen @handle output,
            'select * from #sptables',
            @scrollopt output, @ccopt output, @rows output
        drop table #sptables
    end
    select @ret = isnull(@ret,0)
    return isnull(@ret,0)
go

grant execute on sp_ddopen to public
go

dump tran master with no_log
go

if exists (select * from sysobjects where name = 'sp_configure'
            and sysstat & 0xf = 4)
    begin
        exec sp_configure 'allow updates',0
        reconfigure with override
    end
go

if exists (select * from sysobjects where name = 'sp_check_objects'
            and sysstat & 0xf = 4)
    begin
        /* Only supported on 6.0 servers */
        print ''
        print 'Checking objects created by instcat.sql.'

        exec sp_check_objects 'catalog'
    end
go

print ''
print 'instcat.sql completed successfully.'
go

set quoted_identifier off
go

dump tran master with no_log
go
checkpoint
go