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 master.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
