Post reply

How to find Total Number of Tables in All the databases on a Server?

  • July 9, 2009 at 2:53 pm

    #95558

    Hello All,

    How to find total number of tables in each database for all the databases on one particular server?

    Thanks,

    apat

  • July 9, 2009 at 2:59 pm

    #1022702

    You can use sp_MSForeachdb and a temp table:

    CREATE TABLE #t (DbName NVARCHAR(128), TableCount INT)

    EXECUTE sp_MSforeachdb 'INSERT INTO #t SELECT ''?'', COUNT(*) FROM [?].sys.tables'

    SELECT * FROM #t

    DROP TABLE #t

  • July 9, 2009 at 5:58 pm

    #1022799

    apat (7/9/2009)

    Hello All,

    How to find total number of tables in each database for all the databases on one particular server?

    Thanks,

    apat

    I've seen this question from various folks over the years and I have to finally ask... why do you want to know?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 9, 2009 at 7:18 pm

    #1022818

    Well there was no such need for this to me, I was just curious to know a way to get this....

  • July 10, 2009 at 5:06 am

    #1022950

    Florian Reischl (7/9/2009)

    You can use sp_MSForeachdb and a temp table

    Note that this only lists user-created tables.

    (This may be what is required...but I just thought I'd point it out, for completeness.)

    If you want to see absolutely all tables:

    select [name], schema = SCHEMA_NAME([schema_id]), type_desc, create_date, modify_date, is_ms_shipped

    from sys.all_objects

    where type_desc in (N'INTERNAL_TABLE', N'SYSTEM_TABLE', N'USER_TABLE')

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • July 10, 2009 at 8:26 am

    #1023067

    I'd agree with Jeff. This information doesn't make sense.

    A database is a logical unit of data. While you might need to query across databases, you would never really use the information of all databases together. If you needed this, then you have improperly designed things. You probably should have set up one database with different schemas in that case.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply