Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
	Display all objects in all DBs owned by the Login.

2008-07-06	RBarryYoung	Created.

Test:
	spLogin_OwnedObjects 'sa'
*/
    declare @sql varchar(MAX), @DB_Objects varchar(512)
    Select @DB_Objects = ' L.name  COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User], O.*
     From %D%.sys.objects o
      Join %D%.sys.database_principals u 
            ON Coalesce(o.principal_id, (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
             = U.principal_id
      left join %D%.sys.server_principals L on L.sid = u.sid
'

    Select @sql = 'SELECT * FROM
                    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] = 'master'

    Select @sql = @sql + '
UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] != 'master'

    Select @sql = @sql + ') oo  Where Login = ''' + @Login + ''''

    print @sql
    EXEC (@sql)

ALTER Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
        Display all objects in all DBs owned by the Login.

2008-07-06        RBarryYoung        Created.

Test:
 EXEC spLogin_OwnedObjects 'sa'
*/
    declare @sql varchar(MAX), @DB_Objects varchar(MAX)
    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User]
	, o.name COLLATE DATABASE_DEFAULT as [name]
	, o.object_id
	, o.principal_id
	, o.schema_id
	, o.parent_object_id
	, o.type COLLATE DATABASE_DEFAULT as [type]
	, o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]
	, o.create_date
	, o.modify_date
	, o.is_ms_shipped
	, o.is_published
	, o.is_schema_published
     From %D%.sys.objects o
      Join %D%.sys.database_principals u 
        ON Coalesce(o.principal_id
			 , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
            = U.principal_id
      Left Join %D%.sys.server_principals L on L.sid = u.sid
'

    Select @sql = 'SELECT * FROM
    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] = 'master'

    Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] != 'master'

    Select @sql = @sql + ') oo  Where Login = ''' + @Login + ''''

    print @sql
    EXEC (@sql)

Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
	Display all objects in all DBs owned by the Login.

2008-07-06	RBarryYoung	Created.
2008-08-28	RBarryYoung	Corrected of Windows vs SS default collations.
2008-12-06	RBarryYoung	Fixed for spaces in DB names.

Test:
	spLogin_OwnedObjects 'sa'
*/
    declare @sql varchar(MAX), @DB_Objects varchar(512)
    Select @DB_Objects = ' L.name  COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User], O.*
     From [%D%].sys.objects o
      Join [%D%].sys.database_principals u 
            ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
             = U.principal_id
      left join [%D%].sys.server_principals L on L.sid = u.sid
'

    Select @sql = 'SELECT * FROM
                    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] = 'master'

    Select @sql = @sql + '
UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] != 'master'

    Select @sql = @sql + ') oo  Where Login = ''' + @Login + ''''

    print @sql
    EXEC (@sql)

Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
  Display all objects in all DBs owned by the Login.
 
2008-07-06    RBarryYoung Created.
2008-08-28  RBarryYoung Corrected of Windows vs SS default collations.
2008-12-06    RBarryYoung Fixed for spaces in DB names.
2009-06-01    TBanks Fixed for large quantities of DBs to avoid the 256-table union limit
 
Test:
  spLogin_OwnedObjects 'sa'
*/
set nocount on

create table ##objectowners(
	[DBID] int,
	DBName varchar(255),
	[Login] varchar(255),
	[User] varchar(255),
	name varchar(255),
	[object_id] [int],
	[principal_id] [int],
	[schema_id] [int],
	[parent_object_id] [int],
	[type] [char](2),
	[type_desc] [nvarchar](60),
	[create_date] [datetime],
	[modify_date] [datetime],
	[is_ms_shipped] [bit],
	[is_published] [bit],
	[is_schema_published] [bit])

declare @sql varchar(MAX), 
	@DB_Objects varchar(512)
Select @DB_Objects = ' L.name  COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User], O.*
	From [%D%].sys.objects o
	Join [%D%].sys.database_principals u
	ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
	= U.principal_id
	left join [%D%].sys.server_principals L on L.sid = u.sid
	'

Select @sql = 'insert ##objectowners SELECT * FROM
	(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
	+ Replace(@DB_objects, '%D%', [name])
	From master.sys.databases
	Where [name] = 'master'

select @sql = @sql + case when @Login is null then ') oo'
		else ') oo Where Login = ''' + @Login + ''''
	end
EXEC (@sql)

declare @db varchar(100), 
	@db_id int
DECLARE db_cursor CURSOR FOR
	SELECT name, database_id
	FROM master.sys.databases
	where name <> 'master'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db, @db_id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @sql = 'insert ##objectowners Select '+Cast(@db_id as varchar(9))+', '''+@db+''', '
			+ Replace(@DB_objects, '%D%', @db)
		select @sql = @sql + case when @Login is null then ' '
			else ' Where Login = ''' + @Login + ''''
			end

		exec (@sql)

		FETCH NEXT FROM db_cursor INTO @db, @db_id
	END
CLOSE db_cursor
DEALLOCATE db_cursor

select * from ##objectowners
drop table ##objectowners

set nocount off