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


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

19 comments:

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

This SQL Server 2005 is Easy to use

more feature include in microsoft.

evilripper said...

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

Prawin 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'

Miltiadis said...

Thanks a lot!

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

Atif Rehmat said...
This comment has been removed by the author.
Atif Rehmat 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

ahmed adel said...

wow .. that is wonderful