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

Text File | 1995-12-06 | 25.5 KB | 623 lines

/*
** CheckObj.SQL        1995/11/13 19:35
**
** (From old Upgrade.sql)
**
** Copyright Microsoft, Inc. 1994, 1995, 1996
** All Rights Reserved.
** Use, duplication, or disclosure by the United States Government
** is subject to restrictions as set forth in subdivision (c) (1) (ii)
** of the Rights in Technical Data and Computer Software clause
** at CFR 252.227-7013. Microsoft, Inc. One Microsoft Way, Redmond WA
** 98052.
*/

go
use master
go
dump tran master with no_log
go
set nocount on
go
go

-- - - - -


if exists (select * from sysobjects where name = 'sp_objcheck'
            and sysstat & 0xf = 4)
    begin
    raiserror('Dropping sp_objcheck...',1,1)
    drop procedure sp_objcheck
    end
go

if exists (select * from sysobjects where name = 'sp_check_objects'
            and sysstat & 0xf = 4)
    begin
    raiserror('Dropping sp_check_objects...',1,1)
    drop procedure sp_check_objects
    end
go

if exists (select * from sysobjects
        where name = 'MSobjects'
            and sysstat & 0xf = 3)
    begin
    raiserror('Dropping table MSobjects...',1,1)
    drop table MSobjects
    end
go

print ''
print 'Creating table of Microsoft created objects.'
go

CREATE TABLE MSobjects
(
objectname varchar(30) not null,    -- name of object
object_cat varchar(15) not null,    -- category of object

object_stat int           null        -- Bitmap of where we can expect to find this object:
                    -- 1=master_only ,2=msdb_only ,4=all
)
go

print ''
print 'Inserting names of base system tables.'
go
insert into MSobjects values ('sysalternates','base',4)
go
insert into MSobjects values ('syscharsets','base',1)
insert into MSobjects values ('syscolumns','base',4)
insert into MSobjects values ('syscomments','base',4)
insert into MSobjects values ('sysconfigures','base',1)
insert into MSobjects values ('syscurconfigs','base',1)
insert into MSobjects values ('sysdatabases','base',1)
insert into MSobjects values ('sysdepends','base',4)
insert into MSobjects values ('sysdevices','base',1)
insert into MSobjects values ('sysindexes','base',4)
insert into MSobjects values ('syskeys','base',4)
insert into MSobjects values ('syslanguages','base',1)
insert into MSobjects values ('syslocks','base',1)
insert into MSobjects values ('syslogins','base',1)
insert into MSobjects values ('syslogs','base',4)
insert into MSobjects values ('sysmessages','base',1)
insert into MSobjects values ('sysobjects','base',4)
insert into MSobjects values ('sysprocedures','base',4)
insert into MSobjects values ('sysprocesses','base',1)
insert into MSobjects values ('sysprotects','base',4)
insert into MSobjects values ('sysremotelogins','base',1)
insert into MSobjects values ('syssegments','base',4)
insert into MSobjects values ('sysservers','base',1)
insert into MSobjects values ('systypes','base',4)
insert into MSobjects values ('sysusages','base',1)
insert into MSobjects values ('sysusers','base',4)
go

print ''
print 'Inserting names of catalog objects.'
go
insert into MSobjects values ('sp_column_privileges','catalog',1)
insert into MSobjects values ('sp_columns','catalog',1)
insert into MSobjects values ('sp_databases','catalog',1)
insert into MSobjects values ('sp_datatype_info','catalog',1)
insert into MSobjects values ('sp_fkeys','catalog',1)
insert into MSobjects values ('sp_pkeys','catalog',1)
insert into MSobjects values ('sp_server_info','catalog',1)
insert into MSobjects values ('sp_special_columns','catalog',1)
insert into MSobjects values ('sp_sproc_columns','catalog',1)
insert into MSobjects values ('sp_statistics','catalog',1)
insert into MSobjects values ('sp_stored_procedures','catalog',1)
insert into MSobjects values ('sp_table_privileges','catalog',1)
insert into MSobjects values ('sp_tables','catalog',1)
insert into MSobjects values ('spt_datatype_info','catalog',1)
insert into MSobjects values ('spt_datatype_info_ext','catalog',1)
insert into MSobjects values ('spt_server_info','catalog',1)
go

print ''
print 'Inserting names of ''general'' objects.'
go
insert into MSobjects values ('MS_sqlctrs_users','general',1)
insert into MSobjects values ('sp_abort_xact','general',1)
insert into MSobjects values ('sp_addalias','general',1)
insert into MSobjects values ('sp_addextendedproc','general',1)
insert into MSobjects values ('sp_addgroup','general',1)
insert into MSobjects values ('sp_addlanguage','general',1)
insert into MSobjects values ('sp_addlogin','general',1)
insert into MSobjects values ('sp_addmessage','general',1)
insert into MSobjects values ('sp_addremotelogin','general',1)
insert into MSobjects values ('sp_addsegment','general',1)
insert into MSobjects values ('sp_addserver','general',1)
insert into MSobjects values ('sp_addtype','general',1)
insert into MSobjects values ('sp_addumpdevice','general',1)
insert into MSobjects values ('sp_adduser','general',1)
insert into MSobjects values ('sp_altermessage','general',1)
insert into MSobjects values ('sp_bindefault','general',1)
insert into MSobjects values ('sp_bindrule','general',1)
insert into MSobjects values ('sp_certify_removable','general',1)
insert into MSobjects values ('sp_changedbowner','general',1)
insert into MSobjects values ('sp_changegroup','general',1)
insert into MSobjects values ('sp_check_removable','general',1)
insert into MSobjects values ('sp_checknames','general',1)
insert into MSobjects values ('sp_chklangparam','general',1)
insert into MSobjects values ('sp_commit_xact','general',1)
insert into MSobjects values ('sp_commonkey','general',1)
insert into MSobjects values ('sp_configure','general',1)
insert into MSobjects values ('sp_create_removable','general',1)
insert into MSobjects values ('sp_cursor','general',1)
insert into MSobjects values ('sp_cursorclose','general',1)
insert into MSobjects values ('sp_cursorfetch','general',1)
insert into MSobjects values ('sp_cursoropen','general',1)
insert into MSobjects values ('sp_cursoroption','general',1)
insert into MSobjects values ('sp_coalesce_fragments','general',1)
insert into MSobjects values ('sp_dbinstall','general',1)
insert into MSobjects values ('sp_dboption','general',1)
insert into MSobjects values ('sp_dbremove','general',1)
insert into MSobjects values ('sp_defaultdb','general',1)
insert into MSobjects values ('sp_defaultlanguage','general',1)
insert into MSobjects values ('sp_depends','general',1)
insert into MSobjects values ('sp_devcreate','general',1)
insert into MSobjects values ('sp_devoption','general',1)
insert into MSobjects values ('sp_diskdefault','general',1)
insert into MSobjects values ('sp_dropalias','general',1)
insert into MSobjects values ('sp_dropdevice','general',1)
insert into MSobjects values ('sp_dropextendedproc','general',1)
insert into MSobjects values ('sp_dropgroup','general',1)
insert into MSobjects values ('sp_dropkey','general',1)
insert into MSobjects values ('sp_droplanguage','general',1)
insert into MSobjects values ('sp_droplogin','general',1)
insert into MSobjects values ('sp_dropmessage','general',1)
insert into MSobjects values ('sp_dropremotelogin','general',1)
insert into MSobjects values ('sp_dropsegment','general',1)
insert into MSobjects values ('sp_dropserver','general',1)
insert into MSobjects values ('sp_droptype','general',1)
insert into MSobjects values ('sp_dropuser','general',1)
insert into MSobjects values ('sp_extendsegment','general',1)
insert into MSobjects values ('sp_fixindex','general',1)
insert into MSobjects values ('sp_foreignkey','general',1)
insert into MSobjects values ('sp_help','general',1)
insert into MSobjects values ('sp_helpconstraint','general',1)
insert into MSobjects values ('sp_helpdb','general',1)
insert into MSobjects values ('sp_helpdevice','general',1)
insert into MSobjects values ('sp_helpextendedproc','general',1)
insert into MSobjects values ('sp_helpgroup','general',1)
insert into MSobjects values ('sp_helpindex','general',1)
insert into MSobjects values ('sp_helpjoins','general',1)
insert into MSobjects values ('sp_helpkey','general',1)
insert into MSobjects values ('sp_helplanguage','general',1)
insert into MSobjects values ('sp_helplog','general',1)
insert into MSobjects values ('sp_helpremotelogin','general',1)
insert into MSobjects values ('sp_helprotect','general',1)
insert into MSobjects values ('sp_helpsegment','general',1)
insert into MSobjects values ('sp_helpserver','general',1)
insert into MSobjects values ('sp_helpsort','general',1)
insert into MSobjects values ('sp_helpstartup','general',1)
insert into MSobjects values ('sp_helptext','general',1)
insert into MSobjects values ('sp_helpuser','general',1)
insert into MSobjects values ('sp_lock','general',1)
insert into MSobjects values ('sp_lockinfo','general',1)
insert into MSobjects values ('sp_logdevice','general',1)
insert into MSobjects values ('sp_lookup','general',1)
insert into MSobjects values ('sp_makestartup','general',1)
insert into MSobjects values ('sp_markreport','general',1)
insert into MSobjects values ('sp_monitor','general',1)
insert into MSobjects values ('sp_namecrack','general',1)
insert into MSobjects values ('sp_objectsegment','general',1)
insert into MSobjects values ('sp_password','general',1)
insert into MSobjects values ('sp_placeobject','general',1)
insert into MSobjects values ('sp_primarykey','general',1)
insert into MSobjects values ('sp_probe_xact','general',1)
insert into MSobjects values ('sp_processinfo','general',1)
insert into MSobjects values ('sp_processmail','general',1)
insert into MSobjects values ('sp_recompile','general',1)
insert into MSobjects values ('sp_remoteoption','general',1)
insert into MSobjects values ('sp_remove_xact','general',1)
insert into MSobjects values ('sp_rename','general',1)
insert into MSobjects values ('sp_renamedb','general',1)
insert into MSobjects values ('sp_scan_xact','general',1)
insert into MSobjects values ('sp_serveroption','general',1)
insert into MSobjects values ('sp_setlangalias','general',1)
insert into MSobjects values ('sp_setnetname','general',1)
insert into MSobjects values ('sp_spaceused','general',1)
insert into MSobjects values ('sp_sqlexec','general',1)
insert into MSobjects values ('sp_start_xact','general',1)
insert into MSobjects values ('sp_stat_xact','general',1)
insert into MSobjects values ('sp_unbindefault','general',1)
insert into MSobjects values ('sp_unbindrule','general',1)
insert into MSobjects values ('sp_unmakestartup','general',1)
insert into MSobjects values ('sp_validlang','general',1)
insert into MSobjects values ('sp_validname','general',1)
insert into MSobjects values ('sp_who','general',1)
insert into MSobjects values ('spt_committab','general',1)
insert into MSobjects values ('spt_monitor','general',1)
insert into MSobjects values ('xp_cmdshell','general',1)
insert into MSobjects values ('xp_deletemail','general',1)
insert into MSobjects values ('xp_enumgroups','general',1)
insert into MSobjects values ('xp_findnextmsg','general',1)
insert into MSobjects values ('xp_grantlogin','general',1)
insert into MSobjects values ('xp_logevent','general',1)
insert into MSobjects values ('xp_loginconfig','general',1)
insert into MSobjects values ('xp_logininfo','general',1)
insert into MSobjects values ('xp_msver','general',1)
insert into MSobjects values ('xp_readmail','general',1)
insert into MSobjects values ('xp_revokelogin','general',1)
insert into MSobjects values ('xp_sendmail','general',1)
insert into MSobjects values ('xp_sprintf','general',1)
insert into MSobjects values ('xp_sscanf','general',1)
insert into MSobjects values ('xp_startmail','general',1)
insert into MSobjects values ('xp_stopmail','general',1)
go

dump tran master with no_log
go

print ''
print 'Inserting names of msdb objects.'
go
insert into MSobjects values ('MSWork','msdb',2)
insert into MSobjects values ('NewOrChangedNotification','msdb',2)
insert into MSobjects values ('RemovedNotification','msdb',2)
insert into MSobjects values ('sp_addalert','msdb',2)
insert into MSobjects values ('sp_addnotification','msdb',2)
insert into MSobjects values ('sp_addoperator','msdb',2)
insert into MSobjects values ('sp_addtask','msdb',2)
insert into MSobjects values ('sp_dropalert','msdb',2)
insert into MSobjects values ('sp_dropnotification','msdb',2)
insert into MSobjects values ('sp_dropoperator','msdb',2)
insert into MSobjects values ('sp_droptask','msdb',2)
insert into MSobjects values ('sp_helpalert','msdb',2)
insert into MSobjects values ('sp_helphistory','msdb',2)
insert into MSobjects values ('sp_helpnotification','msdb',2)
insert into MSobjects values ('sp_helpoperator','msdb',2)
insert into MSobjects values ('sp_helptask','msdb',2)
insert into MSobjects values ('sp_MScheckforownedtasks','msdb',2)
insert into MSobjects values ('sp_MStaskparameters','msdb',2)
insert into MSobjects values ('sp_MSsendlargemail','msdb',2)
insert into MSobjects values ('sp_purgehistory','msdb',2)
insert into MSobjects values ('sp_reassigntask','msdb',2)
insert into MSobjects values ('sp_runtask','msdb',2)
insert into MSobjects values ('sp_schedulerlog','msdb',2)
insert into MSobjects values ('sp_schedulerrefresh','msdb',2)
insert into MSobjects values ('sp_schedulersignal','msdb',1)
insert into MSobjects values ('sp_uniquetaskname','msdb',2)
insert into MSobjects values ('sp_updatealert','msdb',2)
insert into MSobjects values ('sp_updatenotification','msdb',2)
insert into MSobjects values ('sp_updateoperator','msdb',2)
insert into MSobjects values ('sp_updatetask','msdb',2)
insert into MSobjects values ('sp_validatealert','msdb',2)
insert into MSobjects values ('sp_verifytask','msdb',2)
insert into MSobjects values ('sp_verifytaskdate','msdb',2)
insert into MSobjects values ('sp_verifytaskid','msdb',2)
insert into MSobjects values ('sp_verifytasksched','msdb',2)
insert into MSobjects values ('sp_verifytasktime','msdb',2)
insert into MSobjects values ('sysalerts','msdb',2)
insert into MSobjects values ('syshistory','msdb',2)
insert into MSobjects values ('syshistory_row_limiter','msdb',2)
insert into MSobjects values ('sysnotifications','msdb',2)
insert into MSobjects values ('sysoperators','msdb',2)
insert into MSobjects values ('systasks','msdb',2)
insert into MSobjects values ('taskrefresh_del','msdb',2)
insert into MSobjects values ('taskrefresh_ins','msdb',2)
insert into MSobjects values ('taskrefresh_upd','msdb',2)
insert into MSobjects values ('sysbackuphistory','msdb',2)
insert into MSobjects values ('sysbackupdetail','msdb',2)
insert into MSobjects values ('sysrestorehistory','msdb',2)
insert into MSobjects values ('sysrestoredetail','msdb',2)
insert into MSobjects values ('sysvolumelabel','msdb',2)
insert into MSobjects values ('sp_get_volume_label','msdb',1)
insert into MSobjects values ('sp_sysbackuphistory','msdb',2)
go

/*
print ''
print 'Inserting names helpsql objects.'
insert into MSobjects values ('helpsql','help',1)
insert into MSobjects values ('sp_helpsql','help',1)
*/

print ''
print 'Inserting names of sqlole objects.'
go
insert into MSobjects values ('sp_MSdbuserprofile','sqlole',1)
insert into MSobjects values ('sp_MSdependencies','sqlole',1)
insert into MSobjects values ('sp_MSfilterclause','sqlole',1)
insert into MSobjects values ('sp_MSforeachdb','sqlole',1)
insert into MSobjects values ('sp_MSforeachtable','sqlole',1)
insert into MSobjects values ('sp_MSforeach_worker','sqlole',1)
insert into MSobjects values ('sp_MSloginmappings','sqlole',1)
insert into MSobjects values ('sp_MSgetalertinfo','sqlole',1)
insert into MSobjects values ('sp_MSgetexecinfo','sqlole',1)
insert into MSobjects values ('sp_MShelpcolumns','sqlole',1)
insert into MSobjects values ('sp_MShelpindex','sqlole',1)
insert into MSobjects values ('sp_MShelptype','sqlole',1)
insert into MSobjects values ('sp_MSindexspace','sqlole',1)
insert into MSobjects values ('sp_MSkilldb','sqlole',1)
insert into MSobjects values ('sp_MSmatchkey','sqlole',1)
insert into MSobjects values ('sp_MSsetalertinfo','sqlole',1)
insert into MSobjects values ('sp_MSsetexecinfo','sqlole',1)
insert into MSobjects values ('sp_MSsettopology','sqlole',1)
insert into MSobjects values ('sp_MSSQLOLE_version','sqlole',1)
insert into MSobjects values ('sp_MSsubscriptions','sqlole',1)
insert into MSobjects values ('sp_MStablechecks','sqlole',1)
insert into MSobjects values ('sp_MStablekeys','sqlole',1)
insert into MSobjects values ('sp_MStablerefs','sqlole',1)
insert into MSobjects values ('sp_MStablespace','sqlole',1)
insert into MSobjects values ('sp_MSuniquename','sqlole',1)
insert into MSobjects values ('sp_MSobjectprivs','sqlole',1)
go

dump tran master with no_log
go

print ''
print 'Inserting names of sysservermessage objects.'
go
insert into MSobjects values ('sysservermessages','servmsgs',2)
go

print ''
print 'Inserting names of replication objects.'
go
insert into MSobjects values ('sp_addarticle','repl',1)
insert into MSobjects values ('sp_addpublication','repl',1)
insert into MSobjects values ('sp_addpublisher','repl',1)
insert into MSobjects values ('sp_addsubscriber','repl',1)
insert into MSobjects values ('sp_addsubscription','repl',1)
insert into MSobjects values ('sp_articlecolumn','repl',1)
insert into MSobjects values ('sp_articlefilter','repl',1)
insert into MSobjects values ('sp_articletextcol','repl',1)
insert into MSobjects values ('sp_textcolstatus','repl',1)
insert into MSobjects values ('sp_articleview','repl',1)
insert into MSobjects values ('sp_changearticle','repl',1)
insert into MSobjects values ('sp_changepublication','repl',1)
insert into MSobjects values ('sp_changesubscriber','repl',1)
insert into MSobjects values ('sp_changesubscription','repl',1)
insert into MSobjects values ('sp_changesubstatus','repl',1)
insert into MSobjects values ('sp_create_distribution_tables','repl',1)
insert into MSobjects values ('sp_distcounters','repl',1)
insert into MSobjects values ('sp_droparticle','repl',1)
insert into MSobjects values ('sp_droppublication','repl',1)
insert into MSobjects values ('sp_droppublisher','repl',1)
insert into MSobjects values ('sp_dropsubscriber','repl',1)
insert into MSobjects values ('sp_dropsubscription','repl',1)
insert into MSobjects values ('sp_dsninfo','repl',1)
insert into MSobjects values ('sp_enumfullsubscribers','repl',1)
insert into MSobjects values ('sp_enumdsn','repl',1)
insert into MSobjects values ('sp_hcchangesubstatus1','repl',1)
insert into MSobjects values ('sp_hcchangesubstatus2','repl',1)
insert into MSobjects values ('sp_helparticle','repl',1)
insert into MSobjects values ('sp_helparticlecolumns','repl',1)
insert into MSobjects values ('sp_helpdistributor','repl',1)
insert into MSobjects values ('sp_helppublication','repl',1)
insert into MSobjects values ('sp_helppublicationsync','repl',1)
insert into MSobjects values ('sp_helpreplicationdb','repl',1)
insert into MSobjects values ('sp_helpsubscriberinfo','repl',1)
insert into MSobjects values ('sp_helpsubscription','repl',1)
insert into MSobjects values ('sp_publishdb','repl',1)
insert into MSobjects values ('sp_replcmds','repl',1)
insert into MSobjects values ('sp_replcounters','repl',1)
insert into MSobjects values ('sp_repldone','repl',1)
insert into MSobjects values ('sp_replflush','repl',1)
insert into MSobjects values ('sp_replica','repl',1)
insert into MSobjects values ('sp_replstatus','repl',1)
insert into MSobjects values ('sp_replsync','repl',1)
insert into MSobjects values ('sp_repltrans','repl',1)
insert into MSobjects values ('sp_subscribe','repl',1)
insert into MSobjects values ('sp_unsubscribe','repl',1)
insert into MSobjects values ('xp_dsninfo','repl',1)
insert into MSobjects values ('xp_enumdsn','repl',1)
go

print ''
print 'Inserting names of starfighter objects.'
go
insert into MSobjects values ('xp_availablemedia','star',1)
insert into MSobjects values ('xp_dirtree','star',1)
insert into MSobjects values ('xp_enumerrorlogs','star',1)
insert into MSobjects values ('xp_enumqueuedtasks','star',1)
insert into MSobjects values ('xp_eventlog','star',1)
insert into MSobjects values ('xp_fixeddrives','star',1)
insert into MSobjects values ('xp_monitorsignal','star',1)
insert into MSobjects values ('xp_perfend','star',1)
insert into MSobjects values ('xp_perfmonitor','star',1)
insert into MSobjects values ('xp_perfsample','star',1)
insert into MSobjects values ('xp_perfstart','star',1)
insert into MSobjects values ('xp_readerrorlog','star',1)
insert into MSobjects values ('xp_regaddmultistring','star',1)
insert into MSobjects values ('xp_regdeletevalue','star',1)
insert into MSobjects values ('xp_regenumvalues','star',1)
insert into MSobjects values ('xp_regread','star',1)
insert into MSobjects values ('xp_regremovemultistring','star',1)
insert into MSobjects values ('xp_regwrite','star',1)
insert into MSobjects values ('xp_schedulersignal','star',1)
insert into MSobjects values ('xp_servicecontrol','star',1)
insert into MSobjects values ('xp_subdirs','star',1)
go

print ''
print 'Inserting names of instmsgs.sql objects.'
go
insert into MSobjects values ('spt_values','sysmsgs',1)
go

print ''
print 'Inserting names of upgrade objects.'
go
insert into MSobjects values ('MSobjects','upgrade',1)
insert into MSobjects values ('sp_check_objects','upgrade',1)
insert into MSobjects values ('sp_db_upgrade','upgrade',1)
insert into MSobjects values ('sp_db_upgrade1','upgrade',1)
insert into MSobjects values ('sp_db_upgrade2','upgrade',1)
insert into MSobjects values ('sp_objcheck','upgrade',1)
insert into MSobjects values ('sysarticles','upgrade',4)
insert into MSobjects values ('sysconstraints','upgrade',4)
insert into MSobjects values ('syspublications','upgrade',4)
insert into MSobjects values ('sysreferences','upgrade',4)
insert into MSobjects values ('syssubscriptions','upgrade',4)
go

print ''
print 'Creating procedure ''sp_objcheck''.'
go
create procedure sp_objcheck
    @objcat varchar(15),
    @omit_cat varchar(15)
as

declare @curdbname varchar(30)
declare @obj_location int
declare @pmsg varchar(255)

select @curdbname = db_name()
/*
** Set mask for bitmap to determine which objects to look for in
** this database.
*/
select @obj_location = case db_name()
    when 'master' then 5    -- Objects in 'master only' (1) plus
                -- objects that are in all databases (4).
    when 'msdb' then 6    -- Objects in 'msdb only' (2) plus
                -- objects that are in all databases (4).
    else 4            -- Only check objects that exist in
                -- all databases.
    end

if exists (select * from master.dbo.MSobjects
        where object_cat like @objcat
            and object_cat <> @omit_cat
            and objectname not in
                (select name from sysobjects)
            and object_stat & @obj_location <> 0)
    begin
        print ''
        print '============================================================================='
        raiserror('ERROR: Missing objects detected in database ''%s''.'
                  ,11,1,@curdbname)
        print ''

        select 'These objects do not exist:' = objectname
        from master.dbo.MSobjects
            where object_cat like @objcat
                and objectname not in
                    (select name from sysobjects)
            and object_stat & @obj_location <> 0

        print '============================================================================='
        raiserror(' ',1,1) with nowait

        deallocate db_name_list_csr

        raiserror('Objects missing.  Aborting script.',20,127) with log
        return(1)
    end
else
    begin
        select @pmsg='No missing Microsoft objects detected in database: '''+
            db_name()+''''
        print ''
        print @pmsg
        update sysobjects set category = category | 2
            where name in
                (select objectname from MSobjects
                    where object_cat like @objcat
                        and object_stat & @obj_location <> 0)
                and id > 100

        checkpoint    -- checkpoint this db while we're here.
        return(0)
    end
go

print ''
print 'Creating procedure ''sp_check_objects''.'
go
create procedure sp_check_objects
    @obj_cat varchar(15) = '%',
    @omit_cat varchar(15) = 'none'
as

declare @dbname varchar(30)
declare @dbsave varchar(30)
declare @pmsg varchar(255)

select @dbsave = db_name()    -- Keep track of where we began so we
                -- can put user back there when done.

if @obj_cat is not null and @obj_cat not in (
        '%',        -- nothing supplied, check all objects
        'base',        -- bldmastr.exe
        'catalog',    -- instcat.sql
        'general',    -- instproc.sql
        'help',        -- helpsql.sql
        'msdb',        -- instmsdb.sql
        'repl',        -- instrepl.sql
        'servmsgs',    -- servmsgs.sql
        'sqlole',    -- sqlole60.sql
        'star',        -- xpstar.sql
        'sysmsgs',    -- instmsgs.sql
        'upgrade')    -- upgrade.sql
    begin
        raiserror('Invalid value entered for @obj_cat',16,1)
        return(1)
    end

declare db_name_list_csr cursor for select name from sysdatabases
open db_name_list_csr

fetch db_name_list_csr into @dbname

while @@fetch_status >= 0

    begin
        declare @dbver int
        dbcc getvalue('current_version')
        select @dbver = @@error        -- Get expected db schema version level
                        -- from server.


            /* See if database has been upgraded, if not, skip check. */
        if (select version from master..sysdatabases where name=@dbname) < @dbver
            begin
                select @pmsg =
                'Database '''+@dbname+
                ''' cannot be checked because it has not been upgraded.'

                print ''
                print @pmsg
                print 'Please use ''sp_db_upgrade <dbname>'' to upgrade the database.'
            end
        else
            exec('use '+@dbname+' exec sp_objcheck '''+@obj_cat+''','''+@omit_cat+'''')

        fetch db_name_list_csr into @dbname    -- Get name of next db
    end

if @dbsave <> db_name()        -- Go back to db we started in.
    exec('use '+@dbsave)

deallocate db_name_list_csr
go

print ''
print 'Now executing ''sp_check_objects''...'
go

exec sp_check_objects 'upgrade'
go

use master
go

print ''
print 'CheckObj.SQL completed.'
go

dump tran master with no_log
go
checkpoint
go
-- -