Liren Large Software Subsidy 5/05.iso/a/a063/21.img/PBSYB.SQL

Text File | 1993-09-14 | 14.3 KB | 560 lines

use master
go
/*
** Drop all existing PB system procedures
*/
drop proc sp_pbcolumn
go
drop proc sp_pbdb
go
drop proc sp_pbindex
go
drop proc sp_pbproc
go
drop proc sp_pbhelprotect
go
drop proc sp_pbtable
go
drop proc sp_pbtext
go
drop proc sp_pbprimarykey
go
drop proc sp_pbforeignkey
go
drop proc sp_pbfktable
go
/*
**  1 - PB stored procedure that retrieves column info
**     from the catalog
*/
create proc sp_pbcolumn @id int as
      select colid, status, type, length, name, usertype
             from dbo.syscolumns  where id = @id
go
 /*
 **  2 - PB stored procedure that retrieves database list
 **     from the catalog
 */
create proc sp_pbdb as
     select name from dbo.sysdatabases
go

/*
**  3 - PB stored procedure that retrieves index info
**     from the catalog
*/
create procedure sp_pbindex
@objname varchar(92)                    /* the table to check for indexes */
as
declare @objid int                      /* the object id of the table */
declare @indid int                      /* the index id of an index */
declare @key1 varchar(30)               /* first key  */
declare @key2 varchar(30)               /* second key  */
declare @key3 varchar(30)               /* third key  */
declare @key4 varchar(30)               /* fourth key  */
declare @key5 varchar(30)               /* ...  */
declare @key6 varchar(30)
declare @key7 varchar(30)
declare @key8 varchar(30)
declare @key9 varchar(30)               /* ...  */
declare @key10 varchar(30)
declare @key11 varchar(30)
declare @key12 varchar(30)
declare @key13 varchar(30)               /* ...  */
declare @key14 varchar(30)
declare @key15 varchar(30)
declare @key16 varchar(30)

declare @unique smallint                /* index is unique */
declare @clustered  smallint            /* index is clustered */

/*
**  Check to see the the table exists and initialize @objid.
*/
select @objid = object_id(@objname)

/*
**  Table doesn't exist so return.
*/
if @objid is NULL
begin
       return
end

/*
**  See if the object has any indexes.
**  Since there may be more than one entry in sysindexes for the object,
**  this select will set @indid to the index id of the first index.
*/
select @indid = min(indid)
       from sysindexes
             where id = @objid
                    and indid > 0
                    and indid < 255

/*
**  If no indexes, return.
*/
if @indid is NULL
begin
       return
end

/*
**  Now check out each index, figure out it's type and keys and
**  save the info in a temporary table that we'll print out at the end.
*/
create table #spindtab
(
       index_name      varchar(30),
       index_num       int,
       index_key1      varchar(30) NULL,
       index_key2      varchar(30) NULL,
       index_key3      varchar(30) NULL,
       index_key4      varchar(30) NULL,
       index_key5      varchar(30) NULL,
       index_key6      varchar(30) NULL,
       index_key7      varchar(30) NULL,
       index_key8      varchar(30) NULL,
       index_key9      varchar(30) NULL,
       index_key10     varchar(30) NULL,
       index_key11     varchar(30) NULL,
       index_key12     varchar(30) NULL,
       index_key13     varchar(30) NULL,
       index_key14     varchar(30) NULL,
       index_key15     varchar(30) NULL,
       index_key16     varchar(30) NULL,
       index_unique    smallint,
       index_clustered smallint
)

while @indid != NULL
begin

       /*
       **  First we'll figure out what the keys are.
       */
       declare @i int
       declare @thiskey varchar(30)
       declare @lastindid int

       select @i = 1

       set nocount on

       while @i <= 16
       begin
             select @thiskey = index_col(@objname, @indid, @i)

             if @thiskey = NULL
             begin
                    goto keysdone
             end

             if @i = 1
             begin
                select @key1 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 2
             begin
                select @key2 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 3
             begin
                select @key3 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 4
             begin
                select @key4 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 5
             begin
                select @key5 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 6
             begin
                select @key6 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 7
             begin
                select @key7 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 8
             begin
                select @key8 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 9
             begin
                select @key9 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 10
             begin
                select @key10 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 11
             begin
                select @key11 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 12
             begin
                select @key12 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 13
             begin
                select @key13 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 14
             begin
                select @key14 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 15
             begin
                select @key15 = index_col(@objname, @indid, @i)
             end
             else
             if @i = 16
             begin
                select @key16 = index_col(@objname, @indid, @i)
             end

             /*
             **  Increment @i so it will check for the next key.
             */
             select @i = @i + 1

       end


       /*
       **  When we get here we now have all the keys.
       */
       keysdone:
             set nocount off

       /*
       **  Figure out if it's a  clustered or nonclustered index.
       */
       if @indid = 1
         select @clustered = 1

       if @indid > 1
         select @clustered = 0

       /*
       **  Now we'll check out the status bits for this index
       */

       /*
       **  See if the index is unique (0x02).
       */
       if exists (select *
             from master.dbo.spt_values v, sysindexes i
                    where i.status & v.number = v.number
                          and v.type = "I"
                          and v.number = 2
                          and i.id = @objid
                          and i.indid = @indid)
         select @unique = 1
       else
         select @unique = 0

       /*
       **  Now we have all the needed info for the index so we'll add
       **  the goods to the temporary table.
       */
       insert into #spindtab
             select name, @i - 1, @key1, @key2, @key3, @key4,
                   @key5, @key6, @key7, @key8, @key9,
                   @key10, @key11, @key12, @key13, @key14,
                   @key15, @key16, @unique, @clustered
                    from sysindexes
                          where id = @objid
                                and indid = @indid
       /*
       **  Now move @indid to the next index.
       */
       select @lastindid = @indid
       select @indid = NULL
       select @indid = min(indid)
             from sysindexes
                    where id = @objid
                          and indid > @lastindid
                          and indid < 255
end

/*
**  Now print out the contents of the temporary index table.
*/
select index_name, index_num, index_key1, index_key2,
      index_key3, index_key4, index_key5, index_key6,
      index_key7, index_key8, index_key9, index_key10,
      index_key11, index_key12, index_key13, index_key14,
      index_key15, index_key16, index_unique, index_clustered
       from #spindtab

drop table #spindtab
go
/*
**  4 - PB stored procedure that retrieves proc info
**     from the catalog
*/
create proc sp_pbproc
          @procid int = NULL ,
          @procnumber smallint = NULL  as
          if @procid = null
            begin
             select o.id, o.name, o.uid, user_name(o.uid),
             p.number from dbo.sysobjects o, dbo.sysprocedures p
               where o.type = 'P' and p.sequence = 0 and o.id = p.id
            end
          else
            begin
             select name, type, length, colid from dbo.syscolumns
             where (id = @procid and number = @procnumber)
            end
          return
go
/*
**  5 - PB stored procedure that retrieves security info
**     from the catalog
*/
create procedure sp_pbhelprotect
            @name varchar(92)         /* name of object or user to check */
            as declare @low int       /* range of userids to check */
            declare @high int
            declare @objid int        /* id of @name if object */
        /* There are two cases handled by this procedure.
        If the first parameter  is an object (table, view, procedure)
        then @name is taken as an object  name and the procedure will
        figure out permissions for the object.
        If the first parameter is not one of the objects
        mentioned it will be taken as user name and all
        the permissions for the user or group name will be shown. */
        /* **  Check to see if it's an object. */

        if exists (select *  from dbo.sysobjects
            where id = object_id(@name)
            and (sysstat & 7 = 1    /* system table */
            or sysstat & 7 = 2      /* view */
            or sysstat & 7 = 3      /* user table */
            or sysstat & 7 = 4))    /* procedure */

            begin

        /*  This is the case where we will show the
           various permissions for an object, possibly restricted by
           a particular user.  */
             select type = substring(b.name, 1, 6), action = a.name,
                   pbuser = substring(user_name(p.uid), 1, 15),
                   column = substring(isnull(col_name(id, c.number),
                          "All"), 1, 31)
               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 dbo.syscolumns
                        where id = object_id(@name))
                            and a.type = "T"
                            and a.number = p.action
                            and b.type = "T"
                            and b.number = p.protecttype
                            and p.id = object_id(@name)
                        order by type, action, pbuser, column
               return
            end

        /* Since @name is not an object let's try it as a user. */

        select @low = uid, @high = uid
           from dbo.sysusers where name = @name
        /*  Now we have the user so run the same
           protection query as before but restrict on user and
           not on object. */
        select distinct type = substring(b.name, 1, 6), action = a.name,
         object = substring(isnull(object_name(p.id), ""), 1, 15),
         column = substring(isnull(col_name(id, c.number), "All"), 1, 10)
         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 <= 255  and a.type = "T"
            and a.number = p.action
            and b.type = "T"
            and b.number = p.protecttype
            and p.uid = @low
           order by type, object, column, action
        return
go
/*
**  6 - PB stored procedure that retrieves table info
**     from the catalog
*/

create procedure sp_pbtable
      @tblname varchar(60) = NULL as
      declare @objid int

      if @tblname = null
        select name, id, type, uid, user_name(uid) from sysobjects where
           (type = 'S' or type = 'U' or type = 'V')
      else
        begin
          select @objid = object_id(@tblname)
          select name, id, type, uid, user_name(uid) from sysobjects
             where id = @objid
        end
      return
go
/*
**  7 - PB stored procedure that retrieves comments info
**     from the catalog
*/

create procedure sp_pbtext
      @objid  int ,
      @number smallint = NULL
      as
      if (@number = NULL)
        select text  from dbo.syscomments where id = @objid
      else
        select text  from dbo.syscomments where
            (id = @objid and number = @number)
      return
go
/*
** 8 - PB stored procedure that retrieves primary key info
**     from the catalog
*/
create procedure sp_pbprimarykey
@tabname varchar(92)                    /* the table to check for indexes */
as
declare @tabid int                      /* the object id of the table */
/*
**  Check to see the the table exists and initialize @objid.
*/
select @tabid = object_id(@tabname)

/*
**  Table doesn't exist so return.
*/
if @tabid is NULL
   begin
      return
   end
else
/*
**  See if the object has a primary key
*/
   begin
      select k.keycnt,
       objectkey1 = col_name(k.id, key1),
       objectkey2 = col_name(k.id, key2),
       objectkey3 = col_name(k.id, key3),
       objectkey4 = col_name(k.id, key4),
       objectkey5 = col_name(k.id, key5),
       objectkey6 = col_name(k.id, key6),
       objectkey7 = col_name(k.id, key7),
       objectkey8 = col_name(k.id, key8)
       from syskeys k, master.dbo.spt_values v
             where  k.type = v.number and v.type =  'K'
             and k.type = 1 and k.id = @tabid
      return
   end
go
/*
** 9 - PB stored procedure that retrieves foreign key info
**     from the catalog
*/
create procedure sp_pbforeignkey
@tabname varchar(92)                    /* the table to check for indexes */
as
declare @tabid int                      /* the object id of the table */
/*
**  Check to see the the table exists and initialize @objid.
*/
select @tabid = object_id(@tabname)
/*
**  Table doesn't exist so return.
*/
if @tabid is NULL
begin
           return
end
else
/*
**  See if the object has any foreign keys
*/
begin
select k.keycnt, OBJECT_NAME(k.depid),
           (select USER_NAME(o.uid) from dbo.sysobjects o
                 where o.id = @tabid),
           objectkey1 = col_name(k.id, key1),
           objectkey2 = col_name(k.id, key2),
           objectkey3 = col_name(k.id, key3),
           objectkey4 = col_name(k.id, key4),
           objectkey5 = col_name(k.id, key5),
           objectkey6 = col_name(k.id, key6),
           objectkey7 = col_name(k.id, key7),
           objectkey8 = col_name(k.id, key8)
           from syskeys k, master.dbo.spt_values v
                         where  k.type = v.number and v.type =  'K'
                         and k.type = 2 and k.id = @tabid
return
end
go
/*
**  10 - PB stored procedure that retrieves table info
**       from foreign keys referencing the current table
*/
create procedure sp_pbfktable
      @tblname varchar(60) = NULL as
      declare @objid int

      if @tblname = null
        return
      else
        begin
          select @objid = object_id(@tblname)
          select name, id, type, uid, user_name(uid) from sysobjects
             where id in (select k.id from syskeys k where k.depid =
            @objid)
        end
      return
go
grant execute on sp_pbcolumn to public
go
grant execute on sp_pbdb to public
go
grant execute on sp_pbindex to public
go
grant execute on sp_pbproc to public
go
grant execute on sp_pbhelprotect to public
go
grant execute on sp_pbtable to public
go
grant execute on sp_pbtext to public
go
grant execute on sp_pbprimarykey to public
go
grant execute on sp_pbforeignkey to public
go
grant execute on sp_pbfktable to public
go