T SQL advice sp_MSforeachdb

  • 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.

  • 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.[/url]

  • 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