How to list the number of tables in a database

  • Hello Everyone,

    I need to list the number of tables in each database on our production server. Can someone tell me how to do this? 

    Thank you so much!

    Juanita

     

  • sp_msforeachdb 'select ''?'' as db_name, count(*) as table_count from ?.dbo.sysobjects where xtype = ''u'''

    That should do it.

  • This query gives the count of user tables and the system tables. Run this query in the database you want to

    select count(*) from sysobjects

    where xtype in ('U','S')

    'U' = user table

    'S' = system table

    if you want just the user tables query with xtype = 'U'.

    Hope thsi helps

  • Thank you for the suggestions! They do the job! 

    Juanita

  • Hi,

    Is there any DMV in SQL 2005 to get this information directly?

    TIA

    Sandesh

  • If you only want user tables, and if you don't mind information schema view (they're system table independent), then the following will work:

    SELECT

     Count(TABLE_NAME)

    FROM

     INFORMATION_SCHEMA.Tables

    WHERE

      TABLE_TYPE = 'BASE TABLE'

     AND TABLE_NAME <> 'dtproperties'

    Note that you have to manually exclude "dtproperties", which is true of the other scripts in this thread as well. For some reason, that is considered a user table even though it's for storing database diagrams. Remove the "Count()" if you want the names themselves. If you want to count system tables as well, then don't use the information schema view method, as those aren't included.

  • Thanks David,

    I will have to run this from databases. If i would like to collect list of all table in all databases present on server, is there any system DMV? I would like to avoid cursor/Temp table/Table variable to write query.

    TIA

    Sandesh

  • I'm not aware of any DMVs that would return everything you need, but only a few of our servers are 2005 at this time, so I haven't had a lot of time to play around with them yet. If you want to run against all databases without writing a cursor, you can use the undocumented "sp_msforeachdb" stored proc that Aaron mentions above (well, it's technically a cursor, but at least you don't have to write it). That stored proc can work with any of the scripts we've provided, so mix and match depending on your needs. If that stored proc is new to you, you'll be happy to know that it has a partner stored proc, named "sp_msforeachtable", which is the same concept, but for all tables within a database.

Viewing 8 posts - 1 through 8 (of 8 total)

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