Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/ppc/object60.sql

Text File | 1995-09-29 | 44.9 KB | 1,240 lines

/***************************************************************************/
/* OBJECT2.SQL - SQL Object Manager INSTALL FOR SQL SERVER VERSION 4.2x    */
/***************************************************************************/
/* latest revision - 3/16/93                                               */
/* Copyright (C) 1993 - Microsoft Corporation                              */
/***************************************************************************/
print ''
print 'Install SQL Object Manager Scripts (SQL Server version 4.2x)'
print ''
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print 'dumping transaction log'
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

use master
go

/*******************************************************************************/
/*        DROP ALL EXISTING PROCEDURE AND TABLES FIRST               */
/*******************************************************************************/
print ''
print 'dropping existing procedures'

if exists (select * from sysobjects where name = 'sp_MScheck_OM' and sysstat & 7 = 4)
begin
  drop procedure sp_MScheck_OM
end

if exists (select * from sysobjects where name = 'sp_MSuser_info' and sysstat & 7 = 4)
begin
  drop procedure sp_MSuser_info
end
go

if exists (select * from sysobjects where name = 'sp_MStable_properties' and sysstat & 7 = 4)
begin
  drop procedure sp_MStable_properties
end
go

if exists (select * from sysobjects where name = 'sp_MScolumn_properties' and sysstat & 7 = 4)
begin
  drop procedure sp_MScolumn_properties
end
go

if exists (select * from sysobjects where name = 'sp_MSobjecttype_name' and sysstat & 7 = 4)
begin
  drop procedure sp_MSobjecttype_name
end
go

if exists (select * from sysobjects where name = 'sp_MSobject_list' and sysstat & 7 = 4)
begin
  drop procedure sp_MSobject_list
end
go

if exists (select * from sysobjects where name = 'sp_MStable_permissions' and sysstat & 7 = 4)
begin
  drop procedure sp_MStable_permissions
end
go

if exists (select * from sysobjects where name = 'sp_MSobject_dependencies' and sysstat & 7 = 4)
begin
  drop procedure sp_MSobject_dependencies
end
go

if exists (select * from sysobjects where name = 'sp_MSOM_version' and sysstat & 7 = 4)
begin
  drop procedure sp_MSOM_version
end
go

/****************************************************************************/
/* This stored procedure returns all relevant user info for current login.  */
/****************************************************************************/

print ''
print 'Creating sp_MSuser_info'

go

create procedure sp_MSuser_info as
   declare @perms int
   select @perms = 0
   set nocount on

   /* CREATE DATABASE */
   if exists (select * from sysprotects where protecttype = 205 and action = 203 and uid = user_id())
   begin
      select @perms = @perms | 1
   end

   /* CREATE DEFAULT */
   if exists (select * from sysprotects where protecttype = 205 and action = 233 and uid = user_id())
   begin
      select @perms = @perms | 2
   end

   /* CREATE PROCEDURE */
   if exists (select * from sysprotects where protecttype = 205 and action = 222 and uid = user_id())
   begin
      select @perms = @perms | 4
   end

   /* CREATE RULE */
   if exists (select * from sysprotects where protecttype = 205 and action = 236 and uid = user_id())
   begin
      select @perms = @perms | 8
   end

   /* CREATE TABLE */
   if exists (select * from sysprotects where protecttype = 205 and action = 198 and uid = user_id())
   begin
      select @perms = @perms | 16
   end

   /* CREATE VIEW */
   if exists (select * from sysprotects where protecttype = 205 and action = 207 and uid = user_id())
   begin
      select @perms = @perms | 32
   end

   set nocount off
   select user_name(), user_id(), @perms
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/****************************************************************************/
/* This stored procedure is an updated version designed to tell users with  */
/* old .exe's to upgrade to the newer ones.                                 */
/****************************************************************************/

print ''
print 'Creating sp_MScheck_OM'

go

create procedure sp_MScheck_OM as
   raiserror 99999 'You must upgrade your SQL Object Manager executable to work with the scripts on this server.'
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/****************************************************************************/
/* This stored procedure will get the table properties for the passed       */
/* table name, according to the type desired in @proptype:                  */
/*    header   = Header information                                         */
/*    key      = Key information                                            */
/*    index    = Index information                                          */
/*    trigger  = Trigger information                                        */
/*    all      = All of the above                                           */
/****************************************************************************/

print ''
print 'Creating sp_MStable_properties'

go

create procedure sp_MStable_properties
@proptype varchar(20) = 'all', @tabname varchar(92) = NULL
as
   declare @my_tabid int
    select @my_tabid = object_id(@tabname)
   if (@my_tabid is null)
   begin
      return (1)
   end

   declare @dash char(5)
   select @dash = '  ---'
   declare @sort_ind int
   declare @sort_name varchar(30)

   /******************* HEADER INFO ****************/
   if (@proptype = 'header' or @proptype = 'all')
   begin
      create table #tempheader (
     indid int null,
     crdate datetime null,
     uid int null,
     tablesize int null,
     tableres int null,
     tableseg int null,
     numcols int null,
     numrows int null,
     rowlen int null)

      declare @t_crdate datetime
      declare @t_uid int
      declare @t_tablesize int
      declare @t_tableres int
      declare @t_numcols int

      /* Size multiplier */
      declare @size_mult int
      select @size_mult = low/1024 from master.dbo.spt_values
     where number = 1 and type = 'E'

      /* Start things off.  Delete just in case there were two rows. */
      insert #tempheader(indid, tableseg, numrows, rowlen)
     select indid, segment, rows, maxlen
        from sysindexes where id = @my_tabid and indid < 2
      delete #tempheader where indid > (select max(indid) from #tempheader)

      select @t_crdate = crdate, @t_uid = uid from sysobjects
        where id = @my_tabid
      update #tempheader set crdate = @t_crdate, uid = @t_uid

      select @t_tablesize = sum(used) * @size_mult from sysindexes
        where id = @my_tabid and indid in (0, 1, 255)
      update #tempheader set tablesize = @t_tablesize

      select @t_tableres = sum(reserved) * @size_mult from sysindexes
        where id = @my_tabid and indid in (0, 1, 255)
      update #tempheader set tableres = @t_tableres

      select @t_numcols = count(*) from syscolumns where id = @my_tabid
      update #tempheader set numcols = @t_numcols

      /* output the results */
      select t.crdate, user_name(t.uid), t.tablesize, t.tableres,
         s.name, t.numcols, t.numrows, t.rowlen
     from #tempheader t, syssegments s
     where s.segment = t.tableseg
   end

   /******************* KEYS ***********************/
   if (@proptype = 'key' or @proptype = 'all')
   begin

      /*
       * Get all keys for this table into a temp table.
       */
      select * into #tempsyskeys
     from syskeys
        where depid = @my_tabid or id = @my_tabid
      if not exists (select * from #tempsyskeys)
      begin
     return (1)
      end

      create table #tempkeys (
     rel_tabid int null,
     keytype int null,
     my_col1 int null,
     my_col2 int null,
     my_col3 int null,
     my_col4 int null,
     my_col5 int null,
     my_col6 int null,
     my_col7 int null,
     my_col8 int null,
     rel_col1 int null,
     rel_col2 int null,
     rel_col3 int null,
     rel_col4 int null,
     rel_col5 int null,
     rel_col6 int null,
     rel_col7 int null,
     rel_col8 int null)

      /*
       * This loads #tempkeys with a my_table ---> related_table combination,
       * which is what sp_helpkey really ought to do.  First get all syskeys
       * rows where this table is the related object.
       */
      insert #tempkeys
     select id, type,
        depkey1, depkey2, depkey3, depkey4, depkey5, depkey6, depkey7, depkey8,
        key1, key2, key3, key4, key5, key6, key7, key8
        from #tempsyskeys
        where depid = @my_tabid

      /*
       * Now we'll change all foreign keys from the above step to primary
       * keys, because they illustrate all key relationships where this is
       * the primary table.
       */
      update #tempkeys set keytype = 1 where keytype = 2

      /*
       * Now get all syskeys rows where this table is the main object.
       * These will be either common keys or keys where this is the foreign
       * table.
       */
      insert #tempkeys
     select depid, type,
        key1, key2, key3, key4, key5, key6, key7, key8,
        depkey1, depkey2, depkey3, depkey4, depkey5, depkey6, depkey7, depkey8
        from #tempsyskeys
     where id = @my_tabid

      /*
       * If there are rows where a foreign table is shown for this table's
       * primary key, then the row which declares this table's primary key
       * with no foreign key table is redundant -- therefore, remove it.
       */
      if exists (select * from #tempkeys
           where keytype = 1 and rel_tabid is not null)
      begin
     delete #tempkeys where keytype = 1 and rel_tabid is null
      end

      /*
       * Now copy them into the format we want -- each row consists of a
       * matching My_col <--> Rel_col pair, and only the first returned row
       * has Key_type and Related_table; others are @dash.
       *
       * Add 10 to #tempkeys.keytype to flag each row after it's processed.
       * @sort_ind, and @sort_name are to keep columns in order within
       * #tempkeyout.
       */
      create table #tempkeyout (
     keytype int null,
     sort_ind int null,
     rel_tabid int null,
     sort_name varchar(30) null,
     rel_tabname varchar(30) null,
     key_typename varchar(30) null,
     my_col int null,
     rel_col int null)

      declare @sort_tabid int
      declare @more_rows int
      select @sort_ind = 1
      select @more_rows = count(*),
         @sort_tabid = min(rel_tabid) from #tempkeys where keytype < 10
      select @sort_name = object_name(@sort_tabid)

      while @more_rows > 0
      begin
     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @sort_name, v.name,
           t.my_col1, t.rel_col1
        from #tempkeys t, master.dbo.spt_values v
        where t.rel_tabid = @sort_tabid
           and t.keytype = v.number and v.type = 'K'
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col2, rel_col2
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col3, rel_col3
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col4, rel_col4
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col5, rel_col5
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col6, rel_col6
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col7, rel_col7
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     insert #tempkeyout
        select keytype, @sort_ind, @sort_tabid, @sort_name, @dash, @dash, my_col8, rel_col8
        from #tempkeys
        where rel_tabid = @sort_tabid
     select @sort_ind = @sort_ind + 1

     /* Flag this row as processed in #tempkeys and set up for the next. */
     update #tempkeys set keytype = keytype + 10 where rel_tabid = @sort_tabid
     select @sort_ind = 1
     select @more_rows = count(*),
        @sort_tabid = min(rel_tabid) from #tempkeys where keytype < 10
     select @sort_name = object_name(@sort_tabid)
      end

      /* Now output it all. */
      select Key_type = key_typename,
         My_Col = col_name(@my_tabid, my_col),
         Related_Table = isnull(rel_tabname, @dash),
         Rel_Col = isnull(col_name(rel_tabid, rel_col), @dash)
     from #tempkeyout
     where my_col is not null
     order by keytype, sort_name, sort_ind
   end

   /******************* INDEXES ********************/
   if (@proptype = 'index' or @proptype = 'all')
   begin
      select i.name, clstr = i.status & 16, uniq = i.status & 2, igduprow = i.status & 4,
         alduprow = i.status & 64, igdupkey = i.status & 1, seg = g.name,
         index_col(@tabname, i.indid, 1), index_col(@tabname, i.indid, 2),
         index_col(@tabname, i.indid, 3), index_col(@tabname, i.indid, 4),
         index_col(@tabname, i.indid, 5), index_col(@tabname, i.indid, 6),
         index_col(@tabname, i.indid, 7), index_col(@tabname, i.indid, 8),
         index_col(@tabname, i.indid, 9), index_col(@tabname, i.indid, 10),
         index_col(@tabname, i.indid, 11), index_col(@tabname, i.indid, 12),
         index_col(@tabname, i.indid, 13), index_col(@tabname, i.indid, 14),
         index_col(@tabname, i.indid, 15), index_col(@tabname, i.indid, 16)
      from sysindexes i, syssegments g where id = object_id(@tabname) and g.segment = i.segment
         and i.indid > 0 and i.indid < 255
      order by clstr DESC, i.name
   end

   /******************* TRIGGERS *******************/
   if (@proptype = 'trigger' or @proptype = 'all')
   begin
      /* Create a temp table with this table's triggers. */
      create table #temptrig (
     trig_id int null,
     isinsert int null,
     isupdate int null,
     isdelete int null)

      declare @ins_id int, @upd_id int, @del_id int
      select @ins_id = instrig, @upd_id = updtrig, @del_id = deltrig
     from sysobjects where id = @my_tabid

      /* INSERT */
      insert #temptrig values (@ins_id, 1, 0, 0)

      /* UPDATE */
      if exists (select trig_id from #temptrig where trig_id = @upd_id)
      begin
     update #temptrig set isupdate = 1 where trig_id = @upd_id
      end
      else
      begin
     insert #temptrig values (@upd_id, 0, 1, 0)
      end

      /* DELETE */
      if exists (select trig_id from #temptrig where trig_id = @del_id)
      begin
     update #temptrig set isdelete = 1 where trig_id = @del_id
      end
      else
      begin
     insert #temptrig values (@del_id, 0, 0, 1)
      end

      select Name = object_name(trig_id), isinsert, isupdate, isdelete
     from #temptrig where trig_id is not null and trig_id > 0
     order by Name
   end

go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/****************************************************************************/
/* This stored procedure will get the column properties for the passed      */
/* table name.  It is a close subset copy from sp_help.                     */
/****************************************************************************/

print ''
print 'Creating sp_MScolumn_properties'

go

create procedure sp_MScolumn_properties
@object_name varchar(92)
as
    create table #sphelptab
    (
        col_name        char (30),
      col_id   tinyint,
        col_type        char (30),
        col_len         tinyint,
        col_status      tinyint,
        col_def         char (30)  null,
        col_dom         char (30)  null,
      usr_type    smallint,
      var_len     bit,
      def_own     char (30) null,
      dom_own     char (30) null
    )

    insert into #sphelptab
        select c.name, c.colid, t.name, c.length, c.status,
        def.name, dom.name,
       t.usertype, 0, user_name(def.uid), user_name(dom.uid)
        from syscolumns c, systypes t, sysobjects def, sysobjects dom
        where c.id = object_id(@object_name)
            and c.usertype *= t.usertype
     and def.id =* c.cdefault and dom.id =* c.domain
    and (def.category & 0x0800 = 0)

    update #sphelptab
        set col_type = 'money'
        where col_type = 'moneyn'

    update #sphelptab
        set col_type = 'float'
        where col_type = 'floatn'

    update #sphelptab
        set col_type = 'int'
        where col_type = 'intn'

    update #sphelptab
        set col_type = 'datetime'
        where col_type = 'datetimn'

   /* These types should match spt_datatype_info_ext. */
   update #sphelptab
      set var_len = 1
      where usr_type in (1,2,3,4,18)

    select t.col_name, t.col_type, t.col_len, Nulls = convert(bit, (t.col_status & 8)),
           t.col_def, t.col_dom, t.var_len, t.def_own, t.dom_own, UDTOwner = user_name(n.uid)
   from #sphelptab t, systypes n
      where t.col_type *= n.name and n.usertype > 99
   order by t.col_id
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/****************************************************************************/
/* This stored procedure will get the name of the passed object type_id.    */
/****************************************************************************/

print ''
print 'Creating sp_MSobjecttype_name'

go

create procedure sp_MSobjecttype_name
@type_id int
as
   if @type_id = -1
   begin
      /*
       * Get all object type names.  Use a temp table because spt_values
       * doesn't have a type 5 and we need to keep this in order.
       */
      create table #tempType (
     number int,
     name char(30)
      )
      insert #tempType
     select number & 7, name
     from master.dbo.spt_values
     where type = 'O' and number >= 0 and number < 9

      /*
       * Insert the mysterious 'logs'; this string could be internationalized
       * but is never used anywhere (yet); it is just a placeholder.
       */
      if not exists (select number from #tempType where number = 5)
      begin
     insert #tempType values (5, 'Not Found')
      end

      /*
       * Insert datatypes as these are not in sysobjects.
       * This string should be internationalized.
       */
      if not exists (select number from #tempType where number = 8)
      begin
     insert #tempType values (8, 'user data type')
      end

      /* Final select to output results */
      select name from #tempType order by number
   end

   else
   begin
      /* Get a specific object type name */
      select name from master.dbo.spt_values
     where type = 'O' and number = @type_id
   end
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/****************************************************************************/
/* This stored procedure will get the name, type, owner, and creation date  */
/* for all objects in the current database.                                 */
/****************************************************************************/

print ''
print 'Creating sp_MSobject_list'

go

create procedure sp_MSobject_list @flags int = 0xffff
as
   /*
    * @flags is a bitmask of power(2, object type number(s)) to return
    * in results set:
    *    0 (1     - 0x0001)    - trigger
    *    1 (2     - 0x0002)    - system table / system objects
    *    2 (4     - 0x0004)    - view
    *    3 (8     - 0x0008)    - user table
    *    4 (16    - 0x0010)    - procedure
    *    5 (32    - 0x0020)    - log
    *    6 (64    - 0x0040)    - default
    *    7 (128   - 0x0080)    - rule
    *    8 (256   - 0x0100)    - datatype
    * If system objects not specified and we're in master, eliminate
    * internal system objects.
    */
    select Name = o.name, Type = v.name, Owner = user_name(o.uid), Created = o.crdate
   from sysobjects o, master.dbo.spt_values v
   where (power(2, o.sysstat & 7) & @flags != 0)
      and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
      and o.sysstat & 7 = v.number
      and v.type = 'O'
      and ((db_id() != 1 or @flags & 2 != 0) or not
         (((o.name = 'helpsql' or o.name like 'MS%'
            or o.name like 'spt[_]%') and o.sysstat & 7 = 3)
          or ((o.name like 'sp[_]%' or o.name like 'xp[_]%'
            or o.name like 'MS[_]%') and o.sysstat & 7 = 4)))
   order by Type, Name
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*****************************************************************************/
/* This stored procedure gets all the users that have particular permissions */
/*****************************************************************************/

print ''
print 'Creating sp_MStable_permissions'

go

create procedure sp_MStable_permissions
@objname varchar(92),
@permit int,
@insertinto bit,
@deletefrom bit,
@executeit bit,
@selcolarray varbinary(32),
@updcolarray varbinary(32)
as

set nocount on

create table #tmp_permiss
(userid int,
 ins     bit,
 del     bit,
 execit  bit,
 selectcols varbinary(32) null,
 updatecols varbinary(32) null
)

declare @objid int
select @objid = object_id(@objname)
declare @granttype int
select @granttype = number from master.dbo.spt_values where name = 'Grant'

/*
 * @permit values:
 *    0 = Revoke  (all users with the specified privileges revoked for @objname)
 *    1 = Grant   (all users with the specified privileges granted for @objname)
 *    2 = None    (all users with no privileges granted for @objname)
 */
if @permit = 2
begin
   select distinct x = t.name, user_name(u.gid) from sysusers t, sysusers u
      where t.uid != 1 and t.uid not in
         (select uid from sysprotects where protecttype = @granttype
         and id = @objid)
      and t.uid = u.uid
      order by x
end
else
begin
   declare @permtype int
   if @permit = 1
       select @permtype = @granttype
   else
       select @permtype = number from master.dbo.spt_values where name = 'Revoke'

   insert into #tmp_permiss
      select distinct uid,0,0,0,NULL,NULL from sysprotects
     where id = @objid and protecttype = @permtype

   update #tmp_permiss set del = 1 from sysprotects where
   userid = uid and protecttype = @permtype and id = @objid
   and action = (select number from master.dbo.spt_values where name = 'Delete')

   update #tmp_permiss set ins = 1 from sysprotects where
   userid = uid and protecttype = @permtype and id = @objid
   and action = (select number from master.dbo.spt_values where name = 'Insert')

   update #tmp_permiss set execit = 1 from sysprotects where
   userid = uid and protecttype = @permtype and id = @objid
   and action = (select number from master.dbo.spt_values where name = 'Execute')

   update #tmp_permiss set updatecols = columns from sysprotects where
   userid = uid and protecttype = @permtype and id = @objid
   and action = (select number from master.dbo.spt_values where name = 'Update')
   and columns = @updcolarray

   update #tmp_permiss set selectcols = columns from sysprotects where
   userid = uid and protecttype = @permtype and id = @objid
   and action = (select number from master.dbo.spt_values where name = 'Select')
   and columns = @selcolarray

   set nocount off

   select x = name, user_name(gid) from #tmp_permiss, sysusers
      where del = @deletefrom and ins = @insertinto and execit= @executeit
      and selectcols=@selcolarray and updatecols=@updcolarray
      and userid = uid
      order by x
end
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*****************************************************************************/
/* This procedure takes a single object, an object type, or a request        */
/* for all objects and returns the ordered hierarchy of parent objects       */
/* (objects which must be created first) or child objects (objects which     */
/* rely on this object being created first).  It iteratively evaluates       */
/* the dependencies of every object it finds, so that every object it        */
/* returns has its entire dependency tree included in the return set.        */
/*****************************************************************************/

print ''
print 'Creating sp_MSobject_dependencies'

go

create procedure sp_MSobject_dependencies
@objname varchar(92) = null, @objtype int = null, @flags int = 0x01fd
as
   if (@objname = '?')
   begin
      print 'sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd'
      print '  name:  name or null (all objects of type)'
      print '  type:  type number (see below) or null'
      print '     if both null, get all objects in database'
      print '  flags is a bitmask of the following values:'
      print '     0x1000  = return multiple parent/child rows per object'
      print '     0x2000  = descending return order'
      print '     0x4000  = return children instead of parents'
      print '     0x8000  = do not return input object'
      print '     power(2, object type number(s))  to return in results set:'
      print '        0 (1     - 0x0001)    - trigger'
      print '        1 (2     - 0x0002)    - system table / system objects'
      print '        2 (4     - 0x0004)    - view'
      print '        3 (8     - 0x0008)    - user table'
      print '        4 (16    - 0x0010)    - procedure'
      print '        5 (32    - 0x0020)    - log'
      print '        6 (64    - 0x0040)    - default'
      print '        7 (128   - 0x0080)    - rule'
      print '        8 (256   - 0x0100)    - datatype'
      print '     shortcuts:'
      print '        29    (0x001d) - trig, view, user table, procedure'
      print '        448   (0x01c0) - rule, default, datatype'
      print '        511   (0x01ff) - all'
      return 0
   end

   if (@objtype in (5, 6, 7, 8))
   begin
      print 'Rules, defaults, and datatypes do not have dependencies.'
      return (1)
   end

   /*
    * Create #t1 and #t2 as temp object holding areas.  Columns are:
    *    tid      - temp object id
    *    ttype    - temp object type
    *    pid      - parent or child object id
    *    ptype    - parent or child object type
    *    bDone    - NULL means dependencies not yet evaluated, else nonNULL.
    */
   declare @curid int
   declare @allobjs int
   declare @delinputobj int
   select @allobjs = 0, @delinputobj = 0, @curid = NULL
   create table #t1 (tid int NULL, ttype smallint NULL, pid int NULL, ptype smallint NULL, bDone smallint NULL)
   create table #t2 (tid int NULL, ttype smallint NULL, pid int NULL, ptype smallint NULL, bDone smallint NULL)
   create table #tempudt (dtype int)

   /*
    * If both name and type are null, this means get every object in the
    * database matching the specification they passed in.  Otherwise,
    * find the passed object or all objects of the passed type.  Start off
    * loading parent info (pid, tid); these will be put into child as needed.
    */
   if (@objname is null and @objtype is null)
   begin
      set nocount on
      select @allobjs = 1
      insert #t1 (pid, ptype) select o.id, o.sysstat & 7 from sysobjects o
         where o.sysstat & 7 not in (6, 7)
         and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
   end else begin
      if (@objname is not null)
      begin
         select @curid = object_id(@objname)
         if (@curid is null)
            return (1)
         if (@flags & 0x8000 != 0)
            select @delinputobj = @curid
         select @objtype = o.sysstat & 7 from sysobjects o where id = @curid
      end

      set nocount on
      if (@curid is null)
         insert #t1 (pid, ptype) select o.id, o.sysstat & 7 from sysobjects o
            where o.sysstat & 7 = @objtype
            and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
      else
         insert #t1 (pid, ptype) values (@curid, @objtype)
   end

   /*
    * All initial objects are loaded as parents/children.  Now we loop, creating
    * rows of child/parent relationships.  Use #t2 as a temp area for the selects
    * to simulate recursion; when they find no rows, we're done with this step.
    *
    * Note that triggers are weird; they're part of a table definition but can
    * also reference other tables, so we need to evaluate them both ways.  SQL
    * Server stores the table for a trigger object as its deltrig; if a trigger
    * references another table, that relationship is stored in sysdepends.
    * This peculiarity of triggers requires separating the object-retrieval pass
    * from the creation-sequence pass (below).
    */
   while (select count(*) from #t1 where bDone is null) > 0
   begin
      /*
       * Remove Microsoft-specific or other system objects from #t1, unless
       * @flags specified including system tables.  We do this here so that
       * cascaded system dependencies are not included unless specifically
       * requested.  For other restrictions, we wait until below so that all
       * cascaded object types are fully evaluated.
       */
      if (@flags & power(2, 1) = 0)
         delete #t1 where ttype = 1 or (db_id() = 1 and
            ((object_name(tid) like 'MS%' and ttype = 3)
            or (object_name(tid) = 'helpsql' and ttype = 3)
            or (object_name(tid) like 'spt[_]%' and ttype = 3)
            or (object_name(tid) like 'sp[_]%' and ttype = 4)
            or (object_name(tid) like 'xp[_]%' and ttype = 4)
            or (object_name(tid) like 'MS[_]%' and ttype = 4)

            or (object_name(pid) like 'MS%' and ptype = 3)
            or (object_name(pid) = 'helpsql' and ttype = 3)
            or (object_name(pid) like 'spt[_]%' and ptype = 3)
            or (object_name(pid) like 'sp[_]%' and ptype = 4)
            or (object_name(pid) like 'xp[_]%' and ptype = 4)
            or (object_name(pid) like 'MS[_]%' and ptype = 4)
            ))

      /* Table --> Triggers */
      if (@flags & power(2, 0) != 0)
         insert #t2 (tid, ttype, pid, ptype)
            select distinct t.pid, t.ptype, o.id, 0 from #t1 t, sysobjects o
               where t.bDone is null and t.ptype = 3 and o.deltrig = t.pid

      if (@flags & 0x4000 != 0)
      begin
         /* Object --> sysdepends children */
         insert #t2 (tid, ttype, pid, ptype)
            select distinct t.pid, t.ptype, d.id, o.sysstat & 7
            from #t1 t, sysdepends d, sysobjects o
            where t.bDone is null and d.depid = t.pid and d.id = o.id
      end else begin
         /* Trigger --> Table */
         if (@flags & power(2, 3) != 0)
            insert #t2 (tid, ttype, pid, ptype)
               select distinct t.pid, t.ptype, o.deltrig, 3 from #t1 t, sysobjects o
                  where t.bDone is null and t.ptype = 0 and o.id = t.pid and o.deltrig != 0

         /* Object --> sysdepends parents */
         insert #t2 (tid, ttype, pid, ptype)
            select distinct t.pid, t.ptype, d.depid, o.sysstat & 7
            from #t1 t, sysdepends d, sysobjects o
            where t.bDone is null and d.id = t.pid and d.depid = o.id
      end

      /*
       * We have this generation of parents in #t2, so clear the current
       * child generation's bDone flags.  Then insert from #t2; the current
       * parent generation becomes the next loop's child generation, with
       * bDone = null until next loop's dependencies are selected.
       */
      update #t1 set bDone = 1
      insert #t1 select * from #t2 where #t2.tid not in
         (select tid from #t1 where #t1.tid = #t2.tid and #t1.pid = #t2.pid)
      truncate table #t2
   end

   /*
    * Because triggers can go in both directions, we'll need to check for
    * circular dependencies on parent evaluation.  Since any tables referenced
    * by the trigger must exist before the trigger can be created, remove rows
    * where the trigger is the parent.
    */
   if (@flags & 0x4000 = 0)
      delete #t1 where ptype = 0

   /*
    * The inner loop above did not put parents with no parents into the
    * child (tid) list.  Do that now, then remove all rows where tid is
    * NULL, because these were initial objects which now have a tid row.
    * Just in case, remove self-refs from #t1, and also remove rows from #t1
    * with NULL pid if a row exists for that tid where the pid is nonNULL.
    */
   insert #t1 (tid, ttype) select distinct pid, ptype from #t1 t
      /* where t.pid not in (select tid from #t1) */
      where not exists (select tid from #t1 where tid = t.pid)
   delete from #t1 where tid is null or tid = pid or (pid is null and tid in
      (select tid from #t1 where pid is not null))

   /*
    * Now get datatypes, which aren't in sysobjects.  Get any types that
    * are referenced by objects in #t1.  We don't care about specific
    * datatype dependencies, we just want to know which ones are needed.
    */
   if (@flags & power(2, 8) != 0)
      insert #tempudt select distinct usertype from syscolumns
         where usertype > 99 and id in (select tid from #t1)

   /*
    * Load rules and defaults needed by datatypes and other #t1 objects
    * into #t2.  Don't track specific object dependencies with these;
    * we just want to know which ones are needed.
    */
   if (@flags & power(2, 7) != 0)
   begin
      insert #t2 (tid, ttype)
         select distinct s.domain, 7 from systypes s, #tempudt t
            where s.domain != 0 and s.usertype = t.dtype
               and s.domain not in (select tid from #t1)
      insert #t2 (tid, ttype)
         select distinct s.domain, 7 from syscolumns s, #t1 t
            where s.domain != 0 and s.id = t.tid
               and s.domain not in (select tid from #t1)
   end
   if (@flags & power(2, 6) != 0)
   begin
      insert #t2 (tid, ttype)
         select distinct s.tdefault, 6 from systypes s, #tempudt t
            where s.tdefault != 0 and s.usertype = t.dtype
               and s.tdefault not in (select tid from #t1)
      insert #t2 (tid, ttype)
         select distinct s.cdefault, 6 from syscolumns s, #t1 t
            where s.cdefault != 0 and s.id = t.tid
               and s.cdefault not in (select tid from #t1)
   end

   /*
    * Now that we've got all objects we want, eliminate those we don't
    * want to return.  If @inputobj and they don't want it returned,
    * remove it from the table.  Then eliminate object types they don't
    * want returned.  Make sure that in doing so we retain all parent
    * objects of the types we do want -- it is possible at this point
    * that a tid we want has no rows except those with pids we don't want.
    */
   if (@flags & 0x01ff != 0x01ff or @delinputobj != 0)
   begin
      delete #t1 where @flags & power(2, ttype) = 0 or tid = @delinputobj
      insert #t1 (tid, ttype) select distinct tid, ttype from #t1
         where (@flags & power(2, ptype) = 0 or pid = @delinputobj)
            and tid not in (select tid from #t1 where ptype is null or
               @flags & power(2, ptype) != 0)
      delete #t1 where @flags & power(2, ptype) = 0 or pid = @delinputobj
   end

   /*
    * To determine creation order, find all objects which are not yet bDone
    * and have no parents or whose parents are all bDone, and set their bDone
    * to the next @curid.  This will leave bDone as the ascending order in
    * which objects must be created (topological sort).
    */
   update #t1 set bDone = 0
   select @curid = 1
   while (select count(*) from #t1 where bDone = 0) > 0
   begin
      if (@flags & 0x4000 != 0)
         update #t1 set bDone = @curid from #t1 tx where bDone = 0
            and not exists
               (select * from #t1 t2 where bDone = 0 and tx.tid = t2.pid)
      else
         update #t1 set bDone = @curid from #t1 tx where bDone = 0
             and not exists
               (select * from #t1 t2 where bDone = 0 and tx.pid = t2.tid)
      select @curid = @curid + 1
   end

   /*
    * Finally, return the objects.  Rules/Defaults must be created first
    * so they're returned first (with a dummy bDone), followed by Datatypes,
    * followed by all other (sysdepends) dependencies.
    *
    * NOTE:  'user data type' should be internationalized to match the
    * string in sp_MSobjecttype_names.
    */
   if (@flags & (power(2, 7) | power(2, 6)) != 0)
      select distinct o.sysstat & 7, o.name, user_name(o.uid), 0
         from sysobjects o, #t2 t
         where o.id = t.tid
         and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
         order by o.sysstat & 7, o.name
   if (@flags & power(2, 8) != 0)
      select distinct 8, c.name, user_name(c.uid), 0
         from systypes c, #tempudt t
         where c.usertype = t.dtype
         order by c.name

   /*
    * Select dependency-style objects, returning parents if desired.
    * @curid will control sort direction.
    */
   /* select @curid = -((@flags & 0x2000) - 1) */
   if (@flags & 0x1000 != 0)
   begin
      if (@flags & 0x02000 != 0)
      begin
         select distinct o.sysstat & 7, o.name, user_name(o.uid), p.sysstat & 7, p.name, user_name(p.uid), t.bDone
            from sysobjects o, sysobjects p, #t1 t
            where o.id = t.tid and p.id =* t.pid
            and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
            order by t.bDone DESC, o.sysstat & 7, o.name
      end else begin
         select distinct o.sysstat & 7, o.name, user_name(o.uid), p.sysstat & 7, p.name, user_name(p.uid), t.bDone
            from sysobjects o, sysobjects p, #t1 t
            where o.id = t.tid and p.id =* t.pid
            and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
            order by t.bDone, o.sysstat & 7, o.name
      end
   end else begin
      if (@flags & 0x02000 != 0)
      begin
         select distinct o.sysstat & 7, o.name, user_name(o.uid), t.bDone
            from sysobjects o, sysobjects p, #t1 t
            where o.id = t.tid
            and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
            order by t.bDone DESC, o.sysstat & 7, o.name
      end else begin
         select distinct o.sysstat & 7, o.name, user_name(o.uid), t.bDone
            from sysobjects o, sysobjects p, #t1 t
            where o.id = t.tid
            and o.sysstat & 0xf < 9 and (o.sysstat != 6 or o.category & 0x0800 = 0)
            order by t.bDone, o.sysstat & 7, o.name
      end
   end
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/************* SQL OM VERSION CONTROL ****************************************/
/*    4.20.xx.yy where yy is script version.                                 */
/*****************************************************************************/

create procedure sp_MSOM_version as
   select 'Microsoft SQL Object Manager script version 6.00.00.1'
go

/* Set the 'MS-installed object' category bit.  Must be done AFTER all objects created. */
print ''
print 'Reconfiguring to set the category bit'
print ''
go
sp_configure 'allow updates',1
go
reconfigure with override
go

update master..sysobjects set category = category | 0x02 where sysstat & 7 = 4 and name in (
   'sp_MScheck_OM',
   'sp_MSuser_info',
   'sp_MStable_properties',
   'sp_MScolumn_properties',
   'sp_MSobject_list',
   'sp_MSobjecttype_name',
   'sp_MStable_permissions',
   'sp_MSobject_dependencies',
   'sp_MSOM_version')
go

sp_configure 'allow updates',0
go
reconfigure with override
go

set nocount on
create table #spmissing
(name varchar(30),
 type varchar(10)
)
go

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSuser_info' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSuser_info', 'procedure')
end

if not exists (select * from master.dbo.sysobjects where name = 'sp_MStable_properties' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MStable_properties', 'procedure')
end

if not exists (select * from master.dbo.sysobjects where name = 'sp_MScolumn_properties' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MScolumn_properties', 'procedure')
end

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSobject_list' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSobject_list', 'procedure')
end

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSobjecttype_name' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSobjecttype_name', 'procedure')
end

if not exists (select * from master.dbo.sysobjects where name = 'sp_MStable_permissions' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MStable_permissions', 'procedure')
end

if not exists (select * from master.dbo.sysobjects where name = 'sp_MSobject_dependencies' and sysstat & 7 = 4)
begin
  insert into #spmissing values('sp_MSobject_dependencies', 'procedure')
end
go

if exists (select * from #spmissing)
begin
   print ''
   print ''
   print ' =====================  ERRORS!  ===================='
   print '       The following objects were not created.'
   print ' Sql Object Manager will not run against this server.'
   print ''
   select * from #spmissing
   drop procedure sp_MSOM_version
end
else
begin
   print ''
   print 'Granting execute permissions on procedures'

   grant execute on sp_MScheck_OM to public
   grant execute on sp_MSuser_info to public
   grant execute on sp_MStable_properties to public
   grant execute on sp_MScolumn_properties to public
   grant execute on sp_MSobject_list to public
   grant execute on sp_MSobjecttype_name to public
   grant execute on sp_MStable_permissions to public
   grant execute on sp_MSobject_dependencies to public
   grant execute on sp_MSOM_version to public

   print ''
   print ''
   print ' Successful installation.'
   exec sp_MSOM_version
end
go
drop table #spmissing
go
set nocount off
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

checkpoint
go