use admin
GO
SET QUOTED_IDENTIFIER OFF 
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:           3-13-2003 
*
*	Project:	utility
*
*	Description:	find user objects in system databases and non dbo owned objects in user databases
*                       
*
*	Usage:
	     EXECUTE admin.dbo.UserObjectsCheck
*       notes: 
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

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%''
				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'






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO