Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T SQL advice sp_MSforeachdb Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 2:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:03 AM
Points: 230, Visits: 1,186
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.
Post #1496580
Posted Thursday, September 19, 2013 3:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 155, Visits: 463
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'



Executive Junior Cowboy Developer, Esq.
Post #1496601
Posted Thursday, September 19, 2013 3:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:03 AM
Points: 230, Visits: 1,186
Wow , thanks very much for quick response. Looks like just what I am after.
Post #1496611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse