if object_Id( 'dbspChkCurrObj') Is Not Null
 drop procedure dbspChkCurrObj
go

CREATE procedure dbspChkCurrObj
as
/*
*************************************************************
Name: dbspChkCurrObj
Description:

Usage:exec dbspChkCurrObj

Author: Steve Jones

Input Params:
-------------

Output Params:
--------------

Return:

Results:
---------

Locals:
--------
@err		Holds error value

Modifications:
--------------

*************************************************************
*/
set nocount on
declare @err int,
	@cmd varchar( 1000),
	@nm varchar( 80)

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @err = -1
 begin
  Raiserror( 'Parameter Error:Usage:exec dbspChkCurrObj', 12, 1)
  return @err
 end

CREATE TABLE #CurrObj
( 	db varchar( 160),
	nm varchar( 160),
	lastdt datetime,
	schema_ver int,
	crdate datetime,
	uid smallint,
	username varchar( 40)
)

declare dbs cursor for
 select name
  from master..sysdatabases
  where name not in ( 'tempdb', 'pubs', 'Northwind')

open dbs

fetch next from dbs into  @nm
while @@fetch_status = 0
 begin
   select @cmd = 'insert #CurrObj  ( db, nm, lastdt, schema_ver, crdate, uid, username ) select ''' + @nm + ''', o.name, getdate(), o.schema_ver, crdate, o.uid, u.name from ' + rtrim( @nm) + '..sysobjects o left outer join ' + rtrim( @nm) + '..sysusers u on o.uid = u.uid'
   exec( @cmd)
  fetch next from dbs into  @nm
 end

deallocate dbs

insert DBALog
select getdate(), 'Schema',
	ISNULL( rtrim( c.db), rtrim( t.db)) + '..' + IsNULL( c.username, 'N/A') + '.' +
	ISNULL( c.nm, t.nm) + ' - Current:' +
	ISNULL( rtrim( cast( c.schema_ver as char( 6))), 'NULL') + ' Last:' +
 	ISNULL( rtrim( cast( t.schema_ver as char( 6))), 'NULL') + ' Created:' + 
 	ISNULL( cast( c.crdate as char( 20) ), cast( t.crdate as char( 20)))
 from #CurrObj c full outer join CurrObj t
	on c.nm = t.nm
	and t.uid = c.uid
   and c.db = t.db
 where ( c.schema_ver != t.schema_ver
			or t.schema_ver Is NULL
			or c.schema_ver Is NULL
			)

drop table #CurrObj

return @err
GO
grant execute on dbspChkCurrObj to WebUser
go
if object_id( 'dbspChkCurrObj') Is Null
 select 'Error:dbspChkCurrObj Not Created'
else
 select 'dbspChkCurrObj Created'
go