June 29, 2008

Count number of tables in a SQL Server database




I got a request from a user and he wanted to count the number of tables in a database. It's quiet simple. Just use the information_schema.tables

USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

' Will return you the count of the tables in the database


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

18 Responses to "Count number of tables in a SQL Server database"
  1. Arjunan M.C.A., said...
    July 22, 2008 at 7:34 AM

    This SQL Server 2005 is Easy to use

    more feature include in microsoft.

  2. evilripper said...
    November 26, 2008 at 1:41 AM

    Nice Tip!! :-D
    It's works fine!
    Thanks!

  3. Prawin said...
    December 4, 2008 at 10:45 AM

    Thanks for the query. Its not working for sample databases such as AdventureWorks db.

    Error: Invalid object name 'information_schema.tables'.

  4. Suprotim Agarwal said...
    December 9, 2008 at 5:56 PM

    Prawin:

    It works.

    USE AdventureWorks
    SELECT COUNT(*) from information_schema.tables
    WHERE table_type = 'base table'

  5. Miltiadis said...
    January 13, 2009 at 2:06 PM

    Thanks a lot!

  6. Elinab said...
    May 4, 2009 at 3:13 AM

    Thank you,
    it works & the sql is not complicated :-)

  7. Andy Santiago said...
    August 11, 2009 at 8:15 AM

    Gracias por el query, funciona bien :) pero deberias especificar que solo cambien el nombre de la BD y nada mas el resto no se toca.


    USE NombredelaBasedeDatos
    SELECT COUNT(*) from information_schema.tables
    WHERE table_type = 'base table'

  8. Jimit said...
    June 24, 2010 at 9:38 PM

    Alternative Query is
    SELECT count(*) from sys.tables where type_desc = 'USER_TABLE'

  9. Ruchir said...
    February 15, 2011 at 1:15 AM

    Thanks...the information proved useful!

  10. mahendra said...
    February 25, 2011 at 2:52 AM

    thank you for the useful information !

  11. cherry...always cooooooool said...
    July 18, 2011 at 2:50 AM

    thanku

  12. Anonymous said...
    September 23, 2011 at 1:04 AM

    select 'Owner'=convert(char(10),t.TABLE_SCHEMA),
    'Table Name'=convert(char(25),t.TABLE_NAME),
    'Record Count'=max(i.rows)
    from sysindexes i, INFORMATION_SCHEMA.TABLES t
    where t.TABLE_NAME = object_name(i.id)
    and t.TABLE_TYPE = 'BASE TABLE'
    group by t.TABLE_SCHEMA, t.TABLE_NAME

  13. Anonymous said...
    October 19, 2011 at 1:13 PM

    Thanks much - very helpful

  14. Anonymous said...
    March 7, 2012 at 3:30 AM

    thats gr8 !

  15. Anonymous said...
    May 3, 2012 at 12:04 AM

    I hav multiple schema in DB.
    How can i identify that result given is for that particular schema n not cumulative?

  16. janardhan said...
    January 13, 2013 at 12:40 PM

    thanx a lot

  17. Atif Rehmat said...
    May 26, 2014 at 5:16 AM
    This comment has been removed by the author.
  18. Atif Rehmat said...
    May 26, 2014 at 5:18 AM

    To get all Tables
    SELECT * from information_schema.tables

    To get specific schema
    SELECT * from information_schema.tables WHERE TABLE_SCHEMA = 'dbo'

    'dbo' is schema name

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions