Liren Large Software Subsidy 5/05.iso/a/a025/9.ddi/INSTALL/INST48.SQL

Text File | 1992-08-24 | 60.3 KB | 1,924 lines

/* @(#) generic/sproc/inscsproc 51.3 7/2/92 */
/*
** INSCSPROC - install a set of stored procedures, and the tables
**        which support them.  This set of procedures is known
**        as the "catalog stored procedures".
**
**    This is the list of procedures:
**            sp_tables
**            sp_statistics
**            sp_columns
**            sp_fkeys
**            sp_pkeys
**            sp_stored_procedures
**            sp_sproc_columns
**            sp_table_privileges
**            sp_column_privileges
**            sp_server_info
**            sp_datatype_info
**            sp_special_columns
**            sp_databases
**
**    These are the tables:
**            spt_datatype_info
**            spt_table_types
**            spt_server_info
**
** History:
**    6/23/92 (craigt) received from Microsoft who developed, tested, and
**            documented these procedures.
*/

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

if exists (select * from sysobjects
           where name = 'sp_configure' and sysstat & 7 = 4)
begin
    execute sp_configure 'update',1
end
reconfigure with override
go
if (exists (select * from sysobjects where name = 'spt_datatype_info'))
    drop table spt_datatype_info
go
if (exists (select * from sysobjects where name = 'spt_table_types'))
    drop table spt_table_types
go
if (exists (select * from sysobjects where name = 'spt_server_info'))
    drop table spt_server_info
go
if (exists (select * from sysobjects where name = 'sp_tables'))
    drop proc sp_tables
go
if (exists (select * from sysobjects where name = 'sp_statistics'))
    drop proc sp_statistics
go
if (exists (select * from sysobjects where name = 'sp_columns'))
    drop proc sp_columns
go
if (exists (select * from sysobjects where name = 'sp_fkeys'))
    drop proc sp_fkeys
go
if (exists (select * from sysobjects where name = 'sp_pkeys'))
    drop proc sp_pkeys
dump tran master with truncate_only
go

go
if (exists (select * from sysobjects where name = 'sp_stored_procedures'))
    drop proc sp_stored_procedures
go
if (exists (select * from sysobjects where name = 'sp_sproc_columns'))
    drop proc sp_sproc_columns
go
if (exists (select * from sysobjects where name = 'sp_table_privileges'))
    drop proc sp_table_privileges
go
if (exists (select * from sysobjects where name = 'sp_column_privileges'))
    drop proc sp_column_privileges
go
if (exists (select * from sysobjects where name = 'sp_server_info'))
    drop proc sp_server_info
go
if (exists (select * from sysobjects where name = 'sp_datatype_info'))
    drop proc sp_datatype_info
go
if (exists (select * from sysobjects where name = 'sp_special_columns'))
    drop proc sp_special_columns
go
if (exists (select * from sysobjects where name = 'sp_databases'))
    drop proc sp_databases
go

dump tran master with truncate_only
go

print "creating table spt_datatype_info"
go
create table spt_datatype_info (ss_dtype       tinyint      not null,
                            type_name        varchar(32)  not null,
                            data_type        smallint     not null,
                            data_precision   int          null,
                            numeric_scale    smallint     null,
                            numeric_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)
go

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf, cp,        nul,case,srch */
values (45,"binary",-2,   null,null,null,255,"0x",null,"max length",1,  1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf, cp, nul,case,srch */
values (50,"bit",-7,        1,   0,   2,null, null,null,null,0,   1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf, cp,       nul,case,srch */
values (47,"char",1,      null,null,null,255,"'", "'","max length",1, 1,   1)

insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (61,"datetime",11,   23,   3,  10,null,"'","'",null,1,  1,   1)

insert into spt_datatype_info
/* ss_type,name,    data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (58,"smalldatetime",11, 23,   3,  10,null,"'","'",null,1,   1,  1)

insert into spt_datatype_info  /* sql server type is "datetimn" */
/* ss_type,name,    data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (111,"datetime",11,     23,   3,   10,null,"'","'",null,1,  1,  1)

insert into spt_datatype_info
/* ss_type, name,data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (62,"float",6,       15,  null,10,null,null,null,null,1,   1,  1)

insert into spt_datatype_info  /* sql server type is "floatn" */
/* ss_type, name,data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (109,"float",6,     15,  null, 10,null, null,null,null,1,  1,  1)

insert into spt_datatype_info
/* ss_type, name,data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (59,"real",7,        7,  null, 10,null, null,null,null,1,  1,  1)

insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values(122,"smallmoney",3,    7,   4,   10,null, null,null,null,1,  1,  1)

insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (56,"int",  4,        10,   0,   10,null,null,null,null,1, 1,  1)

insert into spt_datatype_info  /* sql server type is "intn" */
/* ss_type, name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (38,"int",   4,      10,   0,   10,null,null,null,null,1, 1,  1)

insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (60,"money", 3,       18,  4,    10,null,null,null,null,1, 1,  1)

insert into spt_datatype_info   /* sql server type is "moneyn" */
/* ss_type, name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (110,"money", 3,     18,   4,   10,null,null,null,null,1, 1,  1)

insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len,prf, suf, cp,nul,case,srch */
values (52,"smallint",5,      5,   0,   10,null,null,null,null,1,1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,        prf, suf,cp,nul,case,srch */
values (35,"text",-1,     null, null,null,2147483647,"'","'",null,1,  1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf,   cp,     nul,case,srch */
values (37,"varbinary",-3,null,null,null,254,"0x",null,"max length",1,1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf,   cp,nul,case,srch */
values (48,"tinyint",-6,   3,    0,   10,null,null,null,null,1,  1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf,   cp,    nul,case,srch */
values (39,"varchar",12,   null,null,null,254,"'","'","max length",1,1,   1)

insert into spt_datatype_info
/* ss_type,name,data_type,prec,scale,rdx,len,prf, suf,   cp,nul,case,srch */
values (34,"image",-4,     null,null,null,2147483647,"0x",null,null,1,1,1)
go

print "creating table spt_table_types"
go
create table spt_table_types (ss_type char(1),sag_type varchar(32))
go

insert into spt_table_types
    values ('S','SYSTEM TABLE')
insert into spt_table_types
    values ('U','TABLE')
insert into spt_table_types
    values ('V','VIEW')
go

dump tran master with truncate_only
go

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

insert into spt_server_info
    values (1,"DBMS_NAME","SQL SERVER")
insert into spt_server_info
    values (2,"DBMS_VER","4.2.0")
insert into spt_server_info
    values (6,"DBE_NAME","")
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 (16,"IDENTIFIER_CASE","MIXED")
insert into spt_server_info
    values (15,"COLUMN_LENGTH","30")
insert into spt_server_info
    values (13,"TABLE_LENGTH","30")
insert into spt_server_info
    values (100,"USERID_LENGTH","30")
insert into spt_server_info
    values (17,"TX_ISOLATION","2")
insert into spt_server_info
    values (18,"COLLATION_SEQ","")
insert into spt_server_info
    values (14,"MAX_QUAL_LENGTH","30")
insert into spt_server_info
    values (101,"QUALIFIER_TERM","database")
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 (102,"NAMED_TRANSACTIONS","Y")
insert into spt_server_info
    values (103,"SPROC_AS_LANGUAGE","Y")
insert into spt_server_info
    values (103,"REMOTE_SPROC","Y")
insert into spt_server_info
    values (22,"ACCESSIBLE_TABLES","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")
insert into spt_server_info
    values (110,"DDL_IN_TRANSACTION","N")
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 (500,"SYS_SPROC_VERSION","4.9.1")
go

print "creating sp_tables"
go

create procedure sp_tables(@table_name        varchar(32)  = null,
                           @table_owner     varchar(32)  = null,
                           @table_qualifier varchar(32)  = null,
                           @table_type        varchar(100) = null)
as
    declare @type1 char(1),
            @type2 char(1),
            @type3 char(1),
            @tableindex int
    if @table_qualifier is not null
    begin
        if db_name() != @table_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @table_type is null
    begin
        select @type1 = 'U'
        select @type2 = 'V'
        select @type3 = 'S'
    end
    else
    begin
        if (charindex("'TABLE'",@table_type) != 0)
            select @type1 = 'U'
        else
            select @type1 = 'X'
        if (charindex("'SYSTEM TABLE'",@table_type) != 0)
            select @type2 = 'S'
        else
            select @type2 = 'X'
        if (charindex("'VIEW'",@table_type) != 0)
            select @type3 = 'V'
        else
            select @type3 = 'X'
    end

    if @table_name is null
    begin
        select @table_name = '%'
    end
    else begin
        if (@table_owner is null) and (charindex('%', @table_name) = 0)
        begin
            if exists (select * from sysobjects
                        where uid = user_id()
                        and name = @table_name
                        and (type = 'U' or type = 'V' or type = 'S'))
            begin
                select @table_owner = user_name()
            end
        end
    end
    if @table_owner is null
        select @table_owner = '%'
    select
        table_qualifier = db_name(),
        table_owner=user_name(o.uid),
        table_name=o.name,
        table_type = t.sag_type,
        remarks = convert(varchar(254),null)
    from
        sysusers u,sysobjects o,master.dbo.spt_table_types t
    where
        o.name like @table_name
        and user_name(o.uid) like @table_owner
        and o.type = t.ss_type
        and ( o.type = @type1 or o.type = @type2 or o.type = @type3)
        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    */
            )
go

grant execute on sp_tables to public
go

dump tran master with truncate_only
go

print "creating sp_statistics"
go

dump tran master with truncate_only
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')
AS
    DECLARE @indid                int
    DECLARE @lastindid            int
    DECLARE @full_table_name    char(70)
    if @table_qualifier is not null
    begin
        if db_name() != @table_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @@trancount != 0
    begin
        raiserror 20001 "stored procedure sp_statistics can not be run while in a transaction"
        return
    end
    create table #TmpIndex( table_qualifier varchar(32),
                            table_owner     varchar(32),
                            table_name        varchar(32),
                            index_qualifier varchar(32) null,
                            index_name        varchar(32) null,
                            non_unique        smallint null,
                            type            smallint,
                            seq_in_index    smallint null,
                            column_name     varchar(32) null,
                            collation        char(1) null,
                            remarks         varchar(255) null,
                            index_id        int null,
                            cardinality     int,
                             pages            int,
                             status         smallint)
    if @table_owner is null
    begin
        SELECT @full_table_name = @table_name
    end
    else
    begin
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    SELECT    @indid=min(indid)
    FROM sysindexes
    WHERE id=object_id(@full_table_name)
        AND indid > 0
        AND indid < 255

    WHILE @indid != NULL
    BEGIN
        INSERT #TmpIndex
        SELECT
            DB_NAME(),                               /* table_qualifier */
            USER_NAME(o.uid),                         /* table_owner     */
            o.name,                                   /* table_name      */
            null,                                     /* index_qualifier */
            x.name,                                   /* index_name      */
            0,                                       /* non_unique      */
            3,                                       /* type            */
            colid,                                   /* seq_in_index    */
            INDEX_COL(@full_table_name,indid,colid), /* column_name     */
            "A",                                     /* collation       */
            "",                                       /*  remarks         */
            @indid,                                   /*  index_id        */
             rowcnt(x.doampg),                       /*  cardinality     */
             data_pgs(x.id,doampg),                  /* pages            */
            x.status                                 /* status           */
        FROM sysindexes x,syscolumns c,sysobjects o
        WHERE x.id=object_id(@full_table_name)
            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 = object_id(@full_table_name)
            AND indid > @lastindid
            AND indid < 255
    END

    UPDATE #TmpIndex
        SET non_unique = 1
        WHERE  status&0x2 != 2
    UPDATE #TmpIndex
        SET type = 1
        WHERE  index_id = 1


     /* 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      */
            0,                 /* non_unique      */
            0,                 /* type            */
            0,                 /* seq_in_index    */
            null,               /* column_name     */
            null,               /* collation       */
            "",                 /*  remarks         */
            0,                 /*  index_id        */
            rowcnt(x.doampg),      /*  cardinality     */
            data_pgs(x.id,doampg), /* pages            */
            0                   /* status           */
        FROM sysindexes x, sysobjects o
        WHERE o.id=object_id(@full_table_name)
            AND x.id = o.id
            AND (x.indid=0 or x.indid=1)


    if @is_unique != 'Y'
        SELECT
            table_qualifier,
            table_owner,
            table_name,
            non_unique,
            index_qualifier,
            index_name,
            type,
            seq_in_index,
            column_name,
            collation,
            cardinality,
            pages
         FROM #TmpIndex
        WHERE index_name like @index_name or index_name is null
        ORDER BY non_unique,type,index_name,seq_in_index
    else
        SELECT
            table_qualifier,
            table_owner,
            table_name,
            non_unique,
            index_qualifier,
            index_name,
            type,
            seq_in_index,
            column_name,
            collation,
            cardinality,
            pages
        FROM #TmpIndex
        WHERE non_unique = 0
            and (index_name like @index_name or index_name is null)
        ORDER BY non_unique,type,index_name,seq_in_index
    DROP TABLE #TmpIndex
go

grant execute on sp_statistics to public
go

dump tran master with truncate_only
go

print "creating sp_columns"
go

CREATE PROCEDURE sp_columns (@table_name varchar(32),
                             @table_owner varchar(32) = null,
                             @table_qualifier varchar(32) = null,
                             @column_name varchar(32) = null )
AS
    if @column_name is null
        select @column_name = '%'
    DECLARE @full_table_name    char(70)
    if @table_qualifier is not null
    begin
        if db_name() != @table_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
        if @table_name is null
        begin
            select @table_name='%'
        end
    if @table_owner is null
    begin
        SELECT @full_table_name = @table_name
    end
    else
    begin
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    if (charindex('%',@full_table_name) = 0)
    begin
        SELECT
            table_qualifier = DB_NAME(),
            table_owner = USER_NAME(o.uid),
            table_name = o.name,
            column_name = c.name,
            d.data_type,
            type_name = t.name,
            data_precision=isnull(d.data_precision, convert(int,c.length)),
            length=convert(int,c.length),
            d.numeric_scale,
            d.numeric_radix,
            nullable=convert(smallint, convert(bit, c.status&8)),
            remarks = c.name,
            ss_data_type = c.type
        FROM
        syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
        WHERE
            o.id=object_id(@full_table_name)
            AND c.id = o.id
            AND c.type = d.ss_dtype
        AND c.usertype *= t.usertype
            AND c.name like @column_name
    end else
    begin
        if @table_owner is null
            select @table_owner = '%'
        SELECT
            table_qualifier = DB_NAME(),
            table_owner = USER_NAME(o.uid),
            table_name = o.name,
            column_name = c.name,
            d.data_type,
            d.type_name,
            data_precision=isnull(d.data_precision, convert(int, c.length)),
            length=convert(int,c.length),
            d.numeric_scale,
            d.numeric_radix,
            nullable=convert(smallint, convert(bit, c.status&8)),
            remarks = null,
            ss_data_type = c.type
        FROM
            syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t

        WHERE
            o.name like @table_name
            AND user_name(o.uid) like @table_owner
            AND c.id = o.id
        AND c.usertype *= t.usertype
            AND c.type = d.ss_dtype
            AND c.name like @column_name
    end


go

grant execute on sp_columns to public
go

dump tran master with truncate_only
go

print "creating sp_fkeys"
go

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
    if (@pktable_name is null) and (@fktable_name is null)
    begin
        print "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
            print "Foriegn 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
            print "Primary Key Table qualifier must be name of current database"
            return
        end
    end

    if @pktable_name is null
        select @pktable_name = '%'
    if @pktable_owner is null
        select @pktable_owner = '%'
    if @fktable_name is null
        select @fktable_name = '%'
    if @fktable_owner is null
        select @fktable_owner = '%'

    if @@trancount != 0
    begin
        raiserror 20001 "catalog procedure sp_fkeys can not be run in a transaction"
        return
    end
    create table #fkeys( pktable_qualifier varchar(32),
                         pktable_owner       varchar(32),
                         pktable_name       varchar(32),
                         pkcolumn_name      varchar(32),
                         fktable_qualifier varchar(32),
                         fktable_owner       varchar(32),
                         fktable_name       varchar(32),
                         fkcolumn_name       varchar(32),
                         key_seq           smallint)
    insert into #fkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.depid),
            object_name(k.depid),
            (    select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key1),
             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 = 2
            and c.colid = depkey1
        if (@@rowcount    = 0)
            goto done
        insert into #fkeys
            select
                db_name(),
                (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key2),
                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 = 2
                and c.colid = depkey2
        if (@@rowcount = 0)
            goto done
        insert into #fkeys
            select
                db_name(),
                (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key3),
                 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 = 2
                and c.colid = depkey3
        if (@@rowcount = 0)
            goto done
        insert into #fkeys
        select
            db_name(),
            (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key4),
                 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 = 2
                and c.colid = depkey4
        if (@@rowcount = 0)
            goto done
        insert into #fkeys
            select
                db_name(),
                (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key5),
                 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 = 2
                and c.colid = depkey5
        if (@@rowcount = 0)
            goto done
        insert into #fkeys
            select
                db_name(),
                (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key6),
                 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 = 2
                and c.colid = depkey6
        if (@@rowcount = 0)
            goto done
        insert into #fkeys
            select
                db_name(),
                (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key7),
                 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 = 2
                and c.colid = depkey7
        if (@@rowcount    = 0)
            goto done
        insert into #fkeys
            select
                db_name(),
                (select user_name(uid) from sysobjects o where o.id = k.depid),
                object_name(k.depid),
                (select name
                    from syscolumns
                    where id = k.depid
                    and colid = k.key8),
                 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 = 2
                and c.colid = depkey8
    done:
    select
        pktable_qualifier,
        pktable_owner,
        pktable_name,
        pkcolumn_name,
        fktable_qualifier,
        fktable_owner,
        fktable_name,
        fkcolumn_name,
        key_seq,
        update_delete_rule = 1
    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 fktable_name,fktable_owner,key_seq
go

grant execute on sp_fkeys to public
go
dump tran master with truncate_only
go



print "creating sp_pkeys"
go

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
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @table_owner is null
        select @table_owner = '%'
    if @@trancount != 0
    begin
        raiserror 20001 "catalog procedure sp_pkeys can not be run in a transaction"
        return
    end
    create table #pkeys( table_qualifier varchar(32),
                         table_owner     varchar(32),
                         table_name      varchar(32),
                         column_name     varchar(32),
                         key_seq         smallint,
                         update_delete_rule smallint null )
    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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            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,
            null
        from
            syskeys k,syscolumns c
        where
            c.id = k.id
            and k.type = 1
            and c.colid = key8
    done:
    select
            table_qualifier,
            table_owner,
            table_name,
            column_name,
            key_seq
            from #pkeys
    where table_name like  @table_name
    and table_owner like @table_owner
    order by table_name,table_owner,key_seq
go

grant execute on sp_pkeys to public
go

dump tran master with truncate_only
go

print "creating sp_stored_procedures"
go

create procedure sp_stored_procedures( @sp_name      varchar(32) = null,
                                       @sp_owner     varchar(32) = null,
                                       @sp_qualifier varchar(32) = null)
as
    if @sp_qualifier is not null
    begin
        if db_name() != @sp_qualifier
        begin
            print "stored procedure qualifier must be name of current database"
            return
        end
    end

    if @sp_name is null
    begin
        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')
            begin
                select @sp_owner = user_name()
            end
        end
    end
    if @sp_owner is null
        select @sp_owner = '%'

    select distinct
            sp_qualifier = db_name(),
            sp_owner = user_name(o.uid),
            sp_name = o.name,
            sp_number = p.number,
            sp_num_input_params = -1,
            sp_num_output_params = -1,
            sp_num_result_sets = -1,
            remarks = null
        from
            sysobjects o,sysprocedures p,sysusers u
        where
            o.name like @sp_name
            and user_name(o.uid) like @sp_owner
            and o.type = 'P'
            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    */
                )
go
grant execute on sp_stored_procedures to public
go

dump tran master with truncate_only
go


print "creating sp_sproc_columns"
go

CREATE PROCEDURE sp_sproc_columns( @sp_name      varchar(32),
                                   @sp_owner     varchar(32) = null,
                                   @sp_qualifier varchar(32) = null,
                           @sp_number  smallint = 1,
                                   @column_name  varchar(32) = null )
AS
    DECLARE @full_sp_name char(70)
    if @sp_qualifier is not null
    begin
        if db_name() != @sp_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @sp_owner is null
        SELECT @full_sp_name = @sp_name
    else
        SELECT @full_sp_name = @sp_owner + '.' + @sp_name
    SELECT
        sp_qualifier = DB_NAME(),
        sp_owner = USER_NAME(o.uid),
        sp_name = o.name,
        sp_number  = c.number,
        column_name = c.name,
        d.data_type,
        type_name = t.name,
        c.length,
        d.data_precision,
        d.numeric_scale,
        d.numeric_radix,
        d.nullable,
        remarks = null,
        ss_data_type = c.type,
        column_type = 1
    FROM
        syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
    WHERE o.id=object_id(@full_sp_name)
        AND c.id = o.id
        AND c.type = d.ss_dtype
    AND c.usertype *= t.usertype
        AND c.number = @sp_number
go

grant execute on sp_sproc_columns to public
go


print "creating sp_table_privileges"
go


CREATE PROCEDURE sp_table_privileges (
                        @table_name  varchar(32),
                        @table_owner varchar(32) = null,
                        @table_qualifier varchar(32)= null)
as

    declare @owner_id    int,
            @full_table_name char(70)


    if @table_qualifier is not null
    begin
        if db_name() != @table_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @table_owner is null
    begin
        SELECT @full_table_name = @table_name
    end
    else
    begin
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    if (select object_id(@full_table_name)) is null
    begin
        print "table not found"
        return
    end

    if @@trancount != 0
    begin
        raiserror 20001 "catalog procedure sp_table_privileges can not be run in a transaction"
        return
    end
    create table #table_privileges
            (table_qualifier         varchar(32),
            table_owner         varchar(32),
            table_name     varchar(32),
            grantor            varchar(32),
            grantee            varchar(32),
            select_privilege    int,
            select_grantable    int,
            insert_privilege    int,
            insert_grantable    int,
            update_privilege    int,
            update_grantable    int,
            delete_privilege    int,
            delete_grantable    int,
            references_privilege    int null,
            references_grantable    int null,
            uid                 int,
            gid                 int)

    insert into #table_privileges
    select distinct db_name(),
        user_name(o.uid),
        o.name,
        user_name(o.uid),
        u.name,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        null,
        null,
        u.uid,
        u.gid
    from sysusers u, sysobjects o
    where o.id = object_id(@full_table_name)
    and u.uid != u.gid

    /*
    ** now add row for table owner
    */
    if exists (
        select *
            from #table_privileges
            where grantor = grantee)
    begin
        update #table_privileges
            set select_privilege = 1,
                select_grantable = 1,
                update_privilege = 1,
                update_grantable = 1,
                insert_privilege = 1,
                insert_grantable = 1,
                delete_privilege = 1,
                delete_grantable = 1
            where grantor = grantee
    end
    else
    begin
        insert into #table_privileges
        select  db_name(),
            user_name(o.uid),
            o.name,
            user_name(o.uid),
            user_name(o.uid),
            1,
            1,
            1,
            1,
            1,
            1,
            1,
            1,
            null,
            null,
            o.uid,
            u.gid
        from sysobjects o, sysusers u
        where o.id = object_id(@full_table_name)
        and u.uid = o.uid
    end

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


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

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




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

    select
            table_qualifier,
            table_owner,
            table_name,
            grantor,
            grantee,
            select_privilege,
            select_grantable,
            insert_privilege,
            insert_grantable,
            update_privilege,
            update_grantable,
            delete_privilege,
            delete_grantable,
            references_privilege,
            references_grantable
    from #table_privileges
        where select_privilege != 0
        or insert_privilege != 0
        or delete_privilege != 0
        or update_privilege != 0


go
grant execute on sp_table_privileges to public
go
dump tran master with truncate_only
go

print "creating sp_column_privileges"
go

CREATE PROCEDURE sp_column_privileges (
                        @table_name  varchar(32),
                        @table_owner varchar(32) = null,
                        @table_qualifier varchar(32)= null,
                        @column_name varchar(32) = null)
as

    declare @owner_id    int
    DECLARE @full_table_name    char(70)

    declare @low int            /* range of userids to check */
    declare @high int
    declare @objid int            /* id of @name if object */
    declare @owner_name varchar(32)

    select @low = 0, @high = 32767

    if @column_name is null
        select @column_name = '%'

    if @table_qualifier is not null
    begin
        if db_name() != @table_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @table_owner is null
    begin
        SELECT @full_table_name = @table_name
    end
    else
    begin
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    if (select object_id(@full_table_name)) is null
    begin
        print "table not found"
        return
    end

    if @@trancount != 0
    begin
        raiserror 20001 "catalog procedure sp_column_privileges can not be run in 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_privileges
            (table_qualifier     varchar(32),
            table_owner         varchar(32),
            table_name         varchar(32),
            column_name         varchar(32),
            grantor            varchar(32),
            grantee            varchar(32),
            select_privilege    int,
            select_grantable    int,
            insert_privilege    int,
            insert_grantable    int,
            update_privilege    int,
            update_grantable    int,
            delete_privilege    int,
            delete_grantable    int,
            references_privilege    int null,
            references_grantable    int null,
            uid                 int,
            gid                 int)

/*
** insert a row for the table owner (who has all permissions)
*/
    select @owner_name = (select user_name(uid)
                            from sysobjects
                                where id = object_id(@full_table_name))


    insert into #column_privileges
    select
            db_name(),
            @owner_name,
            @table_name,
            name,
            @owner_name,
            @owner_name,
            1,
            1,
            1,
            1,
            1,
            1,
            1,
            1,
            null,
            null,
            user_id(@owner_name),
            0
        from syscolumns
        where id = object_id(@full_table_name)

/*
** now stick a row in the table 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_privileges
    select distinct
        db_name(),
        user_name(o.uid),
        @table_name,
        c.name,
        user_name(o.uid),
        u.name,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        null,
        null,
        u.uid,
        u.gid
    from sysusers u, syscolumns c, sysobjects o
    where o.id = object_id(@full_table_name)
        and c.id = o.id
        and u.gid != u.uid
        and u.name != @owner_name

    /*
    ** we need to create another temporary table to contain all the various
    ** protection information for the table in question
    */
    create table #protects (uid smallint,
                            action tinyint,
                            protecttype tinyint,
                            name    varchar(32))
    insert into #protects
    select  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 = object_id(@full_table_name))
                and a.type = "T"
                and a.number = p.action
                and b.type = "T"
                and b.number = p.protecttype
                and p.id = object_id(@full_table_name)
                and p.uid between @low and @high


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



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



    update #column_privileges
        set insert_privilege = 1
        where exists (select * from #protects
            where protecttype = 205
            and action = 196
            and ( name = #column_privileges.column_name
                  or name = 'All')
            and ( uid = 0
                  or uid = #column_privileges.gid
                  or uid = #column_privileges.uid))
        and not exists (select * from #protects
            where protecttype = 206
            and action = 196
            and ( name = #column_privileges.column_name
                  or name = 'All')
            and ( uid = 0
                  or uid = #column_privileges.gid
                  or uid = #column_privileges.uid))


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



    select
            table_qualifier,
            table_owner,
            table_name,
            column_name,
            grantor,
            grantee,
            select_privilege,
            select_grantable,
            insert_privilege,
            insert_grantable,
            update_privilege,
            update_grantable,
            delete_privilege,
            delete_grantable,
            references_privilege,
            references_grantable
    from #column_privileges
        where column_name like @column_name
        and ( select_privilege != 0
        or insert_privilege != 0
        or delete_privilege != 0
        or update_privilege != 0)




drop table #column_privileges
drop table #protects
go
grant execute on sp_column_privileges to public
go
dump tran master with truncate_only



print "creating sp_server_info"
if (exists (select * from sysobjects where name = 'sp_server_info'))
    drop proc 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

go

grant execute on sp_server_info to public
go

print "creating sp_datatype_info"
if (exists (select * from sysobjects where name = 'sp_datatype_info'))
    drop proc sp_datatype_info
go

/* the messiness of 'data_type' was to get around the problem of
returning the correct lengths for user defined types.  the join
on the type name ensures all user defined types are returned, but
this puts a null in the data_type column.  by forcing an embedded
select and correlating it with the current row in systypes, we get
the correct data_type mapping even for user defined types  (kwg) */

create proc sp_datatype_info (@data_type int = 0)
as
    if @data_type = 0
        select
            type_name = t.name,
            d.data_type,
            t.length,
            d.literal_prefix,
            d.literal_suffix,
            d.create_params,
            d.nullable,
            d.case_sensitive,
            d.searchable
        from master.dbo.spt_datatype_info d, systypes t
        where d.ss_dtype = t.type and
          t.type not in (111,109,38,110)  /* get rid of nullable types */
    else
        select
            type_name = t.name,
            d.data_type,
            t.length,
            d.literal_prefix,
            d.literal_suffix,
            d.create_params,
            d.nullable,
            d.case_sensitive,
            d.searchable
        from master.dbo.spt_datatype_info d, systypes t
        where data_type = @data_type and
             d.ss_dtype = t.type and
          t.type not in (111,109,38,110)
go

grant execute on sp_datatype_info to public
go

dump tran master with truncate_only
go

print "creating sp_special_columns"
if (exists (select * from sysobjects where name = 'sp_special_columns'))
    drop proc sp_special_columns
go

dump tran master with truncate_only
go


CREATE PROCEDURE sp_special_columns (@table_name      varchar(32),
                             @table_owner      varchar(32) = null,
                             @table_qualifier varchar(32) = null,
                             @col_type       char(1) = 'R')
AS
    DECLARE @indid                int
    DECLARE @full_table_name    char(70)
    DECLARE @msg          char(70)

    if @table_qualifier is not null
    begin
        if db_name() != @table_qualifier
        begin
            print "Table qualifier must be name of current database"
            return
        end
    end
    if @table_owner is null
    begin
        SELECT @full_table_name = @table_name
    end
    else
    begin
        SELECT @full_table_name = @table_owner + '.' + @table_name
    end
    if (select object_id(@full_table_name)) is null
    begin
        print "table not found"
        return
    end

  /* if we just need ROWVER, just run that query */
    if @col_type = 'V'
  BEGIN
    SELECT
        scope = 1,
        column_name = c.name,
        d.data_type,
            type_name = t.name,
            data_precision=isnull(d.data_precision, c.length),
            c.length,
            d.numeric_scale
      FROM
            systypes t,syscolumns c,  master.dbo.spt_datatype_info d
      WHERE
          c.id=object_id(@full_table_name)
          AND c.type = d.ss_dtype
      AND t.type = c.type
      AND t.usertype = 80
    RETURN
  END
  /* now find the id of the 'best' index for this table */

  SELECT @indid = (
    SELECT MIN(indid)
      FROM sysindexes
          WHERE  status&2 = 2
      AND id = object_id(@full_table_name)
      AND indid > 0)

   SELECT
      scope = 3,
      column_name = INDEX_COL(@full_table_name,indid,colid),
      d.data_type,
       d.type_name,
      data_precision=isnull(d.data_precision, c.length),
      c.length,
      d.numeric_scale
    FROM sysindexes x,syscolumns c,  master.dbo.spt_datatype_info d
    WHERE x.id=object_id(@full_table_name)
        AND x.id=c.id
        AND c.colid<keycnt+ (x.status&16)/16
        AND x.indid=@indid
      AND c.type = d.ss_dtype

go
grant execute on sp_special_columns to public
go




print "creating sp_databases"
if (exists (select * from sysobjects where name = 'sp_databases'))
  drop proc sp_databases
go

create procedure sp_databases
        as
        create table #databases ( database_name varchar(32),
                                  size int)
        insert into #databases
        select  name,
            (select sum(size) from master.dbo.sysusages
          where dbid = d.dbid )
        from master.dbo.sysdatabases d
        select database_name, database_size = size*2, remarks = null
        from #databases
go
grant execute on sp_databases to public
go

dump tran master with truncate_only
go






/*******************************************************************************/
/* This portion returns everything back to normal                   */
/*******************************************************************************/
use master
go

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