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