September 19, 2013 at 2:12 pm
Hello everyone.
Can anyone please offer me some advice on this query I am trying to put together?
I want the query to be run on each database on an instance.
I am stuck on getting it to output database name.
EXEC sp_MSforeachdb 'USE ? SELECT ''?'',
'Count' = COUNT(*), 'Type' = CASE type
WHEN 'C' THEN 'CHECK constraints'
WHEN 'D' THEN 'Default or DEFAULT constraints'
WHEN 'F' THEN 'FOREIGN KEY constraints'
WHEN 'FN' THEN 'Scalar functions'
WHEN 'IF' THEN 'Inlined table-functions'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints'
WHEN 'L' THEN 'Logs'
WHEN 'P' THEN 'Stored procedures'
WHEN 'R' THEN 'Rules'
WHEN 'RF' THEN 'Replication filter stored procedures'
WHEN 'S' THEN 'System tables'
WHEN 'TF' THEN 'Table functions'
WHEN 'TR' THEN 'Triggers'
WHEN 'U' THEN 'User tables'
WHEN 'V' THEN 'Views'
WHEN 'X' THEN 'Extended stored procedures'
END, GETDATE()
FROM sysobjects
GROUP BY type
ORDER BY type
GO
many thanks for any advice offered.
September 19, 2013 at 3:13 pm
Firstly, you actually dont need the "Use ?" at the start. sp_msforeachdb effectively assumes that as it iterates through the database.
Also, unless you actually need those strings exactly as you have them, sys.objects has a column called type_desc which should give you the same info. So you could shrink down your query to this (notice the ? before sys.objects):
exec sp_msforeachdb '
select
DBName= ''?'',
ObjCt = count(*),
ObjType = max(type_desc),
RightNow = getdate()
from ?.sys.objects
group by type
order by type'
September 19, 2013 at 3:23 pm
Wow , thanks very much for quick response. Looks like just what I am after.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply