Need to edge out the competition for your dream job? Train for certifications today.
select *,cast(' ' as varchar(100)) As DBName
inner join sys.sysdatabases AS sdb ON sdb.dbid = DB_ID()
where 1 = 2
Open in new window
DROP TABLE #temp
EXEC master..sp_MSForeachdb '
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
select *,"?" AS db
inner join sys.sysdatabases AS sdb ON sdb.dbid = DB_ID(''?'')
//this code below is just to create a temp table to have similar no of columns of sys.objects
select * into #temp from sys.objects
truncate table #temp
//here is just add another column called DBname to hold database name
Alter table #temp
add DBName varchar(100)
//loop through Databases and get sys.objects
insert into #temp
EXEC sp_msforeachdb [select *,"?" AS db from sys.objects]
select * from #temp
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.