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
at

21 comments:

  1. Arjunan M.C.A.,July 22, 2008 at 7:34 AM

    This SQL Server 2005 is Easy to use

    more feature include in microsoft.

    ReplyDelete
  2. AnonymousNovember 26, 2008 at 1:41 AM

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

    ReplyDelete
  3. AnonymousDecember 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'.

    ReplyDelete
  4. Suprotim AgarwalDecember 9, 2008 at 5:56 PM

    Prawin:

    It works.

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

    ReplyDelete
  5. ElinabMay 4, 2009 at 3:13 AM

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

    ReplyDelete
  6. Andy SantiagoAugust 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'

    ReplyDelete
  7. JimitJune 24, 2010 at 9:38 PM

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

    ReplyDelete
  8. RuchirFebruary 15, 2011 at 1:15 AM

    Thanks...the information proved useful!

    ReplyDelete
  9. mahendraFebruary 25, 2011 at 2:52 AM

    thank you for the useful information !

    ReplyDelete
  10. cherry...always coooooooolJuly 18, 2011 at 2:50 AM

    thanku

    ReplyDelete
  11. AnonymousSeptember 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

    ReplyDelete
  12. AnonymousOctober 19, 2011 at 1:13 PM

    Thanks much - very helpful

    ReplyDelete
  13. AnonymousMarch 7, 2012 at 3:30 AM

    thats gr8 !

    ReplyDelete
  14. AnonymousMay 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?

    ReplyDelete
  15. janardhanJanuary 13, 2013 at 12:40 PM

    thanx a lot

    ReplyDelete
  16. UnknownMay 26, 2014 at 5:16 AM

    This comment has been removed by the author.

    ReplyDelete
  17. UnknownMay 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

    ReplyDelete
  18. UnknownOctober 8, 2015 at 6:46 AM

    wow .. that is wonderful

    ReplyDelete
  19. HabeebAugust 18, 2016 at 4:55 AM

    SELECT sobjects.name
    FROM sysobjects sobjects
    WHERE sobjects.xtype = 'U'

    Above is the query that I use.
    For the full list of XTYPE, you can refer:
    SQL Server - Get DB Object Names, Count + XTYPE Reference

    ReplyDelete
  20. UnknownDecember 25, 2016 at 10:42 PM

    HI
    I am Ashwin kumar
    Thanks for this information

    ReplyDelete
  21. UnknownJanuary 26, 2017 at 8:05 AM

    Count the number of tables excluding system tables.


    SELECT count(*) FROM sys.objects WHERE Type_desc= 'USER_TABLE' AND is_ms_shipped <> 1

    ReplyDelete