/*---------------------------------------------------------*/
/*  The "use" stmt below connects you to sybsystemprocs.   */
/*  SPECIAL INSTRUCTIONS FOR WISQL USERS:                  */
/*  WISQL appears to require that the command              */
/*  "use sybsystemprocs" be issued by itself. Once you     */
/*  connect to sybsystemprocs, issue a "file, open,        */
/*  PBSYC.SQL" to read this script into the WISQL command  */
/*  buffer. Next, delete the "use sybsystemprocs" command  */
/*  and the "go" below. Then issue "query, execute all".   */
/*---------------------------------------------------------*/
use sybsystemprocs
go
/*---------------------------------------------------------*/
/*  Drop old PBSYC system procedures from sybsystemprocs.  */
/*  First time installations of the CT-Lib interface       */
/*  do not have to run these "drop procedure" stmts.       */
/*---------------------------------------------------------*/
drop procedure sp_pb40column
go
drop procedure sp_pb40primarykey 
go
drop procedure sp_pb40pkcheck 
go
drop procedure sp_pb40fktable 
go
drop procedure sp_pb40foreignkey 
go
drop procedure sp_pb40extcat 
go
drop procedure sp_pb40proc 
go
drop procedure sp_pb40text 
go
drop procedure sp_pb40db 
go
drop procedure sp_pb40helprotect 
go
drop procedure sp_pb40table 
go
drop procedure sp_pb40index 
go
/*------------------------------------------------*/
/*  PowerBuilder Client Library Interface         */
/*  sp_pb40column lists the columns in a table.   */
/*  The objectid is required as arg1.             */
/*------------------------------------------------*/
create proc sp_pb40column 
   @id int 
as
   select colid, status, type, length, name, usertype, 
          prec,  scale
   	   from dbo.syscolumns  where id = @id
go
/*------------------------------------------------*/
/*  PowerBuilder Client Library Interface         */
/*  sp_pb40primarykey lists the columns that      */
/*  comprise the primary key for a table.  The    */
/*  table name is required as arg1.               */
/*------------------------------------------------*/
create proc sp_pb40primarykey
@@objname varchar(92)
as
declare @@objid   int           /* the object id of the table */
declare @@keyname varchar(30)   /* name of primary key        */
declare @@indid   int           /* the index id of the index  */
declare @@keycnt  smallint      /* number of columns in pk    */

select  @@objid = object_id(@@objname)
if @@objid is NULL
begin 
   return 1
end

select @@keyname = name, 
       @@indid   = indid,
       @@keycnt  = keycnt
 from dbo.sysindexes 
  where id = object_id(@@objname) and
        indid > 0 and 			   /* make sure it is an index         */
        (status2 & 2) = 2 and	   /* make sure declaritive constraint */
		 (status & 2048) = 2048    /* make sure it is primary key      */

if @@keycnt is NULL
begin
   return 1
end

select @@keyname, @@keycnt, 
  index_col(@@objname, @@indid, 1),
  index_col(@@objname, @@indid, 2),  
  index_col(@@objname, @@indid, 3),  
  index_col(@@objname, @@indid, 4),  
  index_col(@@objname, @@indid, 5),  
  index_col(@@objname, @@indid, 6),  
  index_col(@@objname, @@indid, 7),  
  index_col(@@objname, @@indid, 8),  
  index_col(@@objname, @@indid, 9),  
  index_col(@@objname, @@indid, 10),  
  index_col(@@objname, @@indid, 11),  
  index_col(@@objname, @@indid, 12),  
  index_col(@@objname, @@indid, 13),  
  index_col(@@objname, @@indid, 14),  
  index_col(@@objname, @@indid, 15),  
  index_col(@@objname, @@indid, 16)   
go
/*----------------------------------------------------*/
/*  PowerBuilder Client Library Interface             */
/*  sp_pb40pkcheck determines whether or not a table  */
/*  has a Primary Key. Table name is a required arg.  */
/*----------------------------------------------------*/
create procedure sp_pb40pkcheck
   @@objname varchar(92)
as
declare @@stat    int
select  @@stat = sysstat2
   from  dbo.sysobjects
   where id = object_id(@@objname) and
         (sysstat2 & 8) = 8
if (@@stat is NULL)
begin 
   return (0)
end 
else
begin
   return (1)
end
go
/*--------------------------------------------------------------*/
/*  PowerBuilder Client Library Interface                       */
/*  sp_pb40fktable lists the tables that reference this table.  */
/*--------------------------------------------------------------*/
create procedure sp_pb40fktable
  @@objname  varchar(61) = NULL
as 
declare @@objid int

if (@@objname is NULL)
   return (1)

select  @@objid = object_id(@@objname)
select o.name, o.id, o.type, o.uid, user_name(o.uid)
  from   dbo.sysobjects o, dbo.sysreferences r
  where  r.reftabid = @@objid  and
         r.tableid  = o.id 
go
/*-----------------------------------------------------------------*/
/*  PowerBuilder Client Library Interface                          */
/*  sp_pb40foreignkey lists all foreign keys associated with       */
/*  a table whose name is passed as arg1 (required).               */ 
/*-----------------------------------------------------------------*/
create proc sp_pb40foreignkey
@@objname varchar(92)
as
declare @@objid    int           /* the object id of the fk table       */
declare @@keyname  varchar(30)   /* name of foreign key                 */
declare @@constid  int           /* the constraint id in sysconstraints */
declare @@keycnt   smallint      /* number of columns in pk    */
declare @@stat     int

select  @@objid = object_id(@@objname)
if (@@objid is NULL)
begin 
   return (1)
end
select  @@stat = sysstat2
   from  dbo.sysobjects
   where id = @@objid  and
         (sysstat2 & 2) = 2
if (@@stat is NULL)
begin 
   return (1)
end 
/*  Now I know this table has one or more foreign keys.  */
select  o1.name, r.keycnt, o2.name, user_name(o2.uid), 
        r.fokey1,  r.fokey2,  r.fokey3,  r.fokey4,  r.fokey5,  r.fokey6,
        r.fokey7,  r.fokey8,  r.fokey9,  r.fokey10, r.fokey11, r.fokey12,
        r.fokey13, r.fokey14, r.fokey15, r.fokey16
  from  dbo.sysconstraints c,    dbo.sysobjects o1,
        dbo.sysreferences r,     dbo.sysobjects o2
  where c.tableid  =  @@objid    and
        c.status   =  64         and
        c.constrid =  o1.id      and
        o1.type    =  'RI'       and
        c.constrid =  r.constrid and
        r.reftabid =  o2.id 
go
/*-------------------------------------------------*/
/*  PowerBuilder Client Library Interface          */
/*  sp_pb40extcat checks the status of PB Catalog. */
/*  Returns cExists[10] with Y/N values.           */
/*-------------------------------------------------*/
create procedure sp_pb40extcat
as
declare @@tbl       char(1)
declare @@col       char(1)
declare @@fmt       char(1)
declare @@vld       char(1)
declare @@edt       char(1)
declare @@tblproc   char(1)
declare @@colproc   char(1)
declare @@fmtproc   char(1)
declare @@vldproc   char(1)
declare @@edtproc   char(1)
declare @@existbuff char(10)
if exists 
   (select id from dbo.sysobjects where
           id = object_id('dbo.pbcattbl'))
   select @@tbl = 'Y'
else
   select @@tbl = 'N'
if exists 
   (select id from dbo.sysobjects where
           id = object_id('dbo.pbcatcol'))
   select @@col = 'Y'
else
   select @@col = 'N'
if exists 
   (select id from dbo.sysobjects where
           id = object_id('dbo.pbcatfmt'))
   select @@fmt = 'Y'
else
   select @@fmt = 'N'
if exists 
   (select id from dbo.sysobjects where
           id = object_id('dbo.pbcatvld'))
   select @@vld = 'Y'
else
   select @@vld = 'N'
if exists 
   (select id from dbo.sysobjects where
           id = object_id('dbo.pbcatedt'))
   select @@edt = 'Y'
else
   select @@edt = 'N'
if exists
   (select id from dbo.sysobjects where
           id = object_id('dbo.pb_cattbl'))
   select @@tblproc = 'Y'
else
   select @@tblproc = 'N'
if exists
   (select id from dbo.sysobjects where
           id = object_id('dbo.pb_catcol'))
   select @@colproc = 'Y'
else
   select @@colproc = 'N'
if exists
   (select id from dbo.sysobjects where
           id = object_id('dbo.pb_catfmt'))
   select @@fmtproc = 'Y'
else
   select @@fmtproc = 'N'
if exists
   (select id from dbo.sysobjects where
           id = object_id('dbo.pb_catvld'))
   select @@vldproc = 'Y'
else
   select @@vldproc = 'N'
if exists
   (select id from dbo.sysobjects where
           id = object_id('dbo.pb_catedt'))
   select @@edtproc = 'Y'
else
   select @@edtproc = 'N'
select @@existbuff = @@tbl + @@col + @@fmt + @@vld + @@edt + 
                     @@tblproc + @@colproc + @@fmtproc + 
                     @@vldproc + @@edtproc
select @@existbuff
go
/*------------------------------------------------*/
/*  PowerBuilder Client Library Interface         */
/*  sp_pb40proc lists available stored procedures */
/*------------------------------------------------*/
create proc sp_pb40proc
		  @procid int = NULL ,
		  @procnumber smallint = NULL  as
         declare @currdb varchar(31)  
		  if @procid = null
		  begin
		      select @currdb = db_name(db_id())
		      if @currdb = 'sybsystemprocs'
		      begin
			     select o.id, o.name, o.uid, user_name(o.uid),
			        p.number, '1'
			     from dbo.sysobjects o, dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
                union
			     select o.id, o.name, o.uid, user_name(o.uid),
			         p.number, '3'
			     from master.dbo.sysobjects o,
			         master.dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
				       and substring(o.name, 1, 3) = 'sp_'
			     order by 2, 4, 5, 6 
			  end
			  else 
			  if @currdb = 'master'
			  begin
  			     select o.id, o.name, o.uid, user_name(o.uid),
			        p.number, '1'
			     from dbo.sysobjects o, dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
                union
			     select o.id, o.name, o.uid, user_name(o.uid),
			        p.number, '2'
			     from sybsystemprocs.dbo.sysobjects o,
			          sybsystemprocs.dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
				       and substring(o.name, 1, 3) = 'sp_'
			     order by 2, 4, 5, 6 
             end
			  else 
			  begin
  			     select o.id, o.name, o.uid, user_name(o.uid),
			        p.number, '1'
			     from dbo.sysobjects o, dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
                union
			     select o.id, o.name, o.uid, user_name(o.uid),
			        p.number, '2'
			     from sybsystemprocs.dbo.sysobjects o,
			          sybsystemprocs.dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
				       and substring(o.name, 1, 3) = 'sp_'
			     union 
			     select o.id, o.name, o.uid, user_name(o.uid),
			        p.number, '3'
			     from master.dbo.sysobjects o,
			          master.dbo.sysprocedures p
			     where o.type = 'P' and p.sequence = 0 and o.id = p.id
				       and substring(o.name, 1, 3) = 'sp_'
			     order by 2, 4, 5, 6 
			  end
		  end
		  else
		    begin
			 select name, type, length, colid from dbo.syscolumns
			 where (id = @procid and number = @procnumber)
		    end
		  return
go
/*-----------------------------------------------------*/
/*  PowerBuilder Client Library Interface              */
/*  sp_pb40text retrieves the text of a stored         */
/*  procedure from the syscomments table.  requires    */
/*  and @objid argument and an optional @number arg    */
/*-----------------------------------------------------*/
create procedure sp_pb40text
	  @objid  int ,
	  @number smallint = NULL,
	  @db  varchar(2)
	  as
	  if (@number = NULL)
		select text  from dbo.syscomments where id = @objid
	  else
	  begin
	    if @db = '1'
		begin
 		   select text  from dbo.syscomments where
		       (id = @objid and number = @number)
		end
		else
		if @db = '2'
		begin
		   select text from sybsystemprocs.dbo.syscomments where
		       (id = @objid and number = @number)
	  	end
	  	else
		if @db = '3'
		begin
		   select text from master.dbo.syscomments where
		       (id = @objid and number = @number)
	    end
	  end
	  return
go
/*-----------------------------------------------------*/
/*  PowerBuilder Client Library Interface              */
/*  sp_pb40db retrieves the names of all databases     */
/*  available for this server.                         */
/*-----------------------------------------------------*/
create proc sp_pb40db as
	 select name from master.dbo.sysdatabases
go
/*-----------------------------------------------------*/
/*  PowerBuilder Client Library Interface              */
/*  sp_pb40helprotect retrieves security information.  */
/*  @name arg may be an object name or a userid.       */
/*-----------------------------------------------------*/
create procedure sp_pb40helprotect
		    @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
/*-----------------------------------------------------*/
/*  PowerBuilder Client Library Interface              */
/*  sp_pb40table 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_pb40table
	  @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
/*-----------------------------------------------------*/
/*  PowerBuilder Client Library Interface              */
/*  sp_pb40index retrieves info about all indexes for  */
/*  a specific table (@objname is required).           */
/*-----------------------------------------------------*/
create procedure sp_pb40index
@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
/*--------------------------------------------------------*/
/*  Grant public execute access for these procedures.     */
/*  Optionally, an sa might create a group for PB users   */
/*  and grant execute to this group rather than public.   */  
/*--------------------------------------------------------*/
grant execute on sp_pb40column to public
go
grant execute on sp_pb40primarykey to public
go
grant execute on sp_pb40pkcheck to public
go
grant execute on sp_pb40fktable to public
go
grant execute on sp_pb40foreignkey to public
go
grant execute on sp_pb40extcat to public
go
grant execute on sp_pb40proc to public
go
grant execute on sp_pb40text to public
go
grant execute on sp_pb40db to public
go
grant execute on sp_pb40helprotect to public
go
grant execute on sp_pb40table to public
go
grant execute on sp_pb40index to public
go
