Get DBObjects with Specified Owner

-- Proc_GetDBObjectsWithSpecifiedObjectOwner 'surveyuser'

Create Proc Proc_GetDBObjectsWithSpecifiedObjectOwner (@ObjectOwner nvarchar(517))
as
Begin
	if exists (select name from tempdb..sysobjects where name like '#tmpSP_TablesOutput%')
	Begin
		drop table #tmpSP_TablesOutput
	End
	
	create table #tmpSP_TablesOutput
	(
	TABLE_QUALIFIER sysname,
	TABLE_OWNER sysname,
	TABLE_NAME sysname, 
	TABLE_TYPE varchar(32), 
	REMARKS varchar(254) 
	)
	insert into #tmpSP_TablesOutput
		exec sp_tables
	
	if exists (select name from tempdb..sysobjects where name like '#tmpSP_stored_proceduresOutput%')
	Begin
		drop table #tmpSP_stored_proceduresOutput
	End
	
	create table #tmpSP_stored_proceduresOutput
	(
	PROCEDURE_QUALIFIER sysname,
	PROCEDURE_OWNER sysname,
	PROCEDURE_NAME nvarchar(134),
	NUM_INPUT_PARAMS int,
	NUM_OUTPUT_PARAMS int, 
	NUM_RESULT_SETS int,
	REMARKS varchar(254), 
	PROCEDURE_TYPE smallint 
	)
	insert into #tmpSP_stored_proceduresOutput
		Exec sp_stored_procedures
	
	if exists (select name from tempdb..sysobjects where name like '#tmpObjectOwnerDetails%')
	Begin
		drop table #tmpObjectOwnerDetails
	End
	
	create table #tmpObjectOwnerDetails
	(
	AutoID int identity,
	ObjectName nvarchar(255),
	ObjectOwner sysname,
	ObjectType varchar(32)
	)

	insert into #tmpObjectOwnerDetails (ObjectName, ObjectOwner, ObjectType)
		select TABLE_NAME, TABLE_OWNER, TABLE_TYPE  from #tmpSP_TablesOutput where TABLE_OWNER = @ObjectOwner
	
	insert into #tmpObjectOwnerDetails (ObjectName, ObjectOwner, ObjectType)
		select  PROCEDURE_NAME, PROCEDURE_OWNER, 'Procedure' from #tmpSP_stored_proceduresOutput where PROCEDURE_OWNER = @ObjectOwner
	
	select * from #tmpObjectOwnerDetails
End