-- Stored Procedure: dbo.UserObjectsCheck
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserObjectsCheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserObjectsCheck]
GO



CREATE PROCEDURE [dbo].[UserObjectsCheck] 
AS
/*******************************************************************************************************
* Admin.dbo.UserObjectsCheck 
* Creator:       Bill Wunder
* Date:          ?
*
*
* Description:   Checks for user objects in system dbs and user objects in user dbs not owned by dbo
* Notes:         
*
* Usage:
      EXECUTE Admin.dbo.UserObjectsCheck
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*      Scott Nelson    7/17/03  updated to exclude extended stored procedures in system dbs.
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------

exec sp_msforeachdb 'use ? 
if ''?'' in (''master'',''model'',''msdb'') 
 begin
  if exists (select 1 from sysobjects where status >= 0)
   begin
    print ''''
    print ''User Objects in System Database ?''
    print ''''
    select  cast(coalesce(v.name,o.type) as varchar(25)) as [Object Type], 
     cast(user_name(objectproperty(o.id,''OwnerId'')) + ''.'' + o.name as varchar(50)) as [Object Name], 
     cast(o.crdate as varchar(20)) as [Create Date]
    from sysobjects o
    left join master.dbo.spt_values v
    on o.type = substring(v.name, 1, datalength(o.Type))
    where o.status >= 0 
    and v.type = ''O9T''
    and o.Name not like ''%log_shipp%''
    and o.Name not like ''%RTbl%''
    and o.Name not like ''xp_%''
    order by o.type, o.name
   end
  else
   print ''No User objects found in ?''
 end
else
 begin
  if exists (select 1 from sysobjects where uid not in (1,3) and name not like ''fn%'') 
   begin
    print ''''
    print ''Objects Not Owned by [dbo] in User Database ?''
    print ''''
    select user_name(uid) + ''.'' + name as ''?'' 
    from sysobjects 
    where uid not in (1,3) 
    and name not like ''fn%''
   end
  else
   print ''All objects in ? belong to dbo''
 end'

RETURN


GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO