get database objects information for all databases on SQL Server

-- Part 1

Declare @sqlstr nvarchar(200) 

-- Part 2

/* drop the temporary table if already exists */

If Object_Id('tempdb..#tblDBObjects') is Not Null

Drop table #tblDBObjects

/* create temporary table */

Create TABLE #tblDBObjects (

dbName sysname,

objName varchar(200),

objtype char(2),

objtypename varchar(200),

crdate datetime,

)

-- Part 3

/*assign string value to variable */

Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, sysobjects.name, xtype,type_desc,crdate From ?..sysobjects join sys.objects on sysobjects.id=sys.objects.object_id'''

/* execute SQL string */

Exec sp_executesql @sqlstr

-- Part 4

/* select from temp table */

Select * From #tblDBObjects order by crdate desc

RETURN