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
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
USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'
' Will return you the count of the tables in the database
Subscribe to:
Post Comments (Atom)
21 comments:
This SQL Server 2005 is Easy to use
more feature include in microsoft.
Nice Tip!! :-D
It's works fine!
Thanks!
Thanks for the query. Its not working for sample databases such as AdventureWorks db.
Error: Invalid object name 'information_schema.tables'.
Prawin:
It works.
USE AdventureWorks
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'
Thank you,
it works & the sql is not complicated :-)
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'
Alternative Query is
SELECT count(*) from sys.tables where type_desc = 'USER_TABLE'
Thanks...the information proved useful!
thank you for the useful information !
thanku
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
Thanks much - very helpful
thats gr8 !
I hav multiple schema in DB.
How can i identify that result given is for that particular schema n not cumulative?
thanx a lot
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
wow .. that is wonderful
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
HI
I am Ashwin kumar
Thanks for this information
Count the number of tables excluding system tables.
SELECT count(*) FROM sys.objects WHERE Type_desc= 'USER_TABLE' AND is_ms_shipped <> 1
Post a Comment