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

CREATE procedure dbspLoadCurrObj
as
/*
*************************************************************
Name: dbspLoadCurrObj
Description:
   This stored procedure loads the CurrObj table with the current
information about all objects in the database.

Usage:exec dbspLoadCurrObj

Author: Steve Jones

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

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

Return:

Results:
---------

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

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

*************************************************************
*/
set nocount on
declare @err int

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

delete CurrObj

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

declare @cmd varchar( 1000), @nm varchar( 80)
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

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