io Programmo 6/IOPROG_06.ISO/trial/pb/server/pbsyb.sql

Text File | 1996-06-07 | 19.2 KB | 674 lines

/*---------------------------------------------------------*/
/*  The "use" stmt below connects you to master.           */
/*  If you are installing on a Sybase System 10 server,    */
/*  we recommend that you "use sybsystemprocs" instead.    */
/*  SPECIAL INSTRUCTIONS FOR WISQL USERS:                  */
/*  WISQL appears to require that the command              */
/*  "use database" be issued by itself. Once you           */
/*  connect to the appropriate database (master or         */
/*  sybsystemprocs), issue a "file, open, PBSYB.SQL" to    */
/*  read this script into the WISQL command buffer.        */
/*  Next, delete the "use" command and the "go" below.     */
/*  Then issue "query, execute all".                       */
/*                                                         */
/*  By default, execute authority for these procedures is  */
/*  granted to "public".  You may search and replace       */
/*  "public"  with a group-id if you wish tigher security. */
/*---------------------------------------------------------*/
use master
go

/*---------------------------------------------------------*/
/*  Drop old PBSYB system procedures                       */
/*---------------------------------------------------------*/
if exists (select 1 from sysobjects
           where name = "sp_pbcolumn" and type = "P")
begin
   drop procedure sp_pbcolumn
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbdb" and type = "P")
begin
   drop procedure sp_pbdb
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbindex" and type = "P")
begin
   drop procedure sp_pbindex
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbproc" and type = "P")
begin
   drop procedure sp_pbproc
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbhelprotect" and type = "P")
begin
   drop procedure sp_pbhelprotect
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbtable" and type = "P")
begin
   drop procedure sp_pbtable
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbtext" and type = "P")
begin
   drop procedure sp_pbtext
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbprimarykey" and type = "P")
begin
   drop procedure sp_pbprimarykey
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbforeignkey" and type = "P")
begin
   drop procedure sp_pbforeignkey
end
go
if exists (select 1 from sysobjects
           where name = "sp_pbfktable" and type = "P")
begin
   drop procedure sp_pbfktable
end
go

/*------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                 */
/*  sp_pbcolumn lists the columns in a table.     */
/*  The objectid is required as arg1.             */
/*------------------------------------------------*/
create proc sp_pbcolumn @id int as
      select colid, status, type, length, name, usertype
             from dbo.syscolumns  where id = @id
go
if @@error = 0
begin
   grant execute on sp_pbcolumn to public
end
go

/*-----------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                      */
/*  sp_pbdb retrieves the names of all databases       */
/*  available for this server.                         */
/*-----------------------------------------------------*/
create proc sp_pbdb as
     select name from master.dbo.sysdatabases
go
if @@error = 0
begin
   grant execute on sp_pbdb to public
end
go

/*-----------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                      */
/*  sp_pbindex retrieves info about all indexes for    */
/*  a specific table (@objname is required).           */
/*-----------------------------------------------------*/
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
if @@error = 0
begin
   grant execute on sp_pbindex to public
end
go

/*------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                 */
/*  sp_pbproc lists available stored procedures   */
/*------------------------------------------------*/
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),
             c.number from dbo.sysobjects o, dbo.syscomments c
               where o.type = 'P' and o.id = c.id  and c.colid = 1
            end
          else
            begin
             select name, type, length, colid from dbo.syscolumns
             where (id = @procid and number = @procnumber)
            end
          return
go
if @@error = 0
begin
   grant execute on sp_pbproc to public
end
go

/*-----------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                      */
/*  sp_pbhelprotect retrieves security information.    */
/*  @name arg may be an object name or a userid.       */
/*-----------------------------------------------------*/
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
if @@error = 0
begin
   grant execute on sp_pbhelprotect to public
end
go

/*-----------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                      */
/*  sp_pbtable retrieves info about all tables in a    */
/*  database (if @tblname is NULL) or info about a     */
/*  specific table (if @tblname ispassed as arg1).     */
/*-----------------------------------------------------*/
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
if @@error = 0
begin
   grant execute on sp_pbtable to public
end
go

/*-----------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                      */
/*  sp_pbtext retrieves the text of a stored           */
/*  procedure from the syscomments table.  requires    */
/*  and @objid argument and an optional @number arg    */
/*-----------------------------------------------------*/
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
if @@error = 0
begin
   grant execute on sp_pbtext to public
end
go


/*------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                 */
/*  sp_pbprimarykey lists the columns that        */
/*  comprise the primary key for a table.  The    */
/*  table name is required as arg1.               */
/*  This routine lists syskeys entries, not the   */
/*  declarative referential integrity feature in  */
/*  Sybase SQL Server Version 10.                 */
/*------------------------------------------------*/
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
if @@error = 0
begin
   grant execute on sp_pbprimarykey to public
end
go


/*-----------------------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                                  */
/*  sp_pbforeignkey lists all foreign keys associated with         */
/*  a table whose name is passed as arg1 (required).               */
/*  This procedure lists entries in syskeys, not the declarative   */
/*  referential integrity foreign keys that System 10 provides.    */
/*-----------------------------------------------------------------*/
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
if @@error = 0
begin
   grant execute on sp_pbforeignkey to public
end
go

/*--------------------------------------------------------------*/
/*  PowerBuilder DB-Lib Interface                               */
/*  sp_pbfktable lists the tables that reference this 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
if @@error = 0
begin
   grant execute on sp_pbfktable to public
end
go