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


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

21 comments:

Arjunan M.C.A., said...

This SQL Server 2005 is Easy to use

more feature include in microsoft.

Anonymous said...

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

Anonymous said...

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

Error: Invalid object name 'information_schema.tables'.

Suprotim Agarwal said...

Prawin:

It works.

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

Elinab said...

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

Andy Santiago said...

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'

Jimit said...

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

Ruchir said...

Thanks...the information proved useful!

mahendra said...

thank you for the useful information !

cherry...always cooooooool said...

thanku

Anonymous said...

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

Anonymous said...

Thanks much - very helpful

Anonymous said...

thats gr8 !

Anonymous said...

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

janardhan said...

thanx a lot

Unknown said...
This comment has been removed by the author.
Unknown said...

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

Unknown said...

wow .. that is wonderful

Habeeb said...

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

Unknown said...

HI
I am Ashwin kumar
Thanks for this information

Unknown said...

Count the number of tables excluding system tables.


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