hI, CAN ANYONE GIVE ME THE SQL STATEMENT TO COUNT THE NUMBER OF TABLES IN SQL SERVER ?

Question

Post reply

hI, CAN ANYONE GIVE ME THE SQL STATEMENT TO COUNT THE NUMBER OF TABLES IN SQL SERVER ?

sivark1

SSCrazy

Points: 2654
More actions
June 22, 2009 at 1:11 am

#201519

HI,
CAN ANYONE GIVE ME THE SQL STATEMENT TO COUNT THE NUMBER OF TABLES IN a Database /SQLSERVER 2000/2005 ?
Thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

select count(*) from sysindexes where indid in (0,1)

Edit: This will return all tables including system tables.

Check

select object_name(id) from sysindexes where indid in (0,1)

If you need user tables only, use syobjects instead

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ps. One Orange Chip Points: 29422 More actions · Answer 2

select * from information_schema.tables

OR

select * from sysobjects where xtype='u'

This will give table details from a specific database.

Pradeep Singh

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

for SQL Server 2005

--------------------

select count (*) from sys.sysobjects where xtype ='U'

select count (*) from sys.objects where type ='U'

select count (*) from INFORMATION_SCHEMA.TABLES where table_type='base table'

for SQL Server 2000

--------------------

select count (*) from sysobjects where xtype ='U'

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Actually, for SQL 2005 the easiest way is to use sys.tables.

SELECT COUNT(*) FROM sys.tables

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RichB SSCrazy Eights Points: 9651 More actions · Answer 5

GilaMonster (6/22/2009)
Actually, for SQL 2005 the easiest way is to use sys.tables.
SELECT COUNT(*) FROM sys.tables

Which is fine, as long as you remember that it also includes the occasional system table.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

RichardB (6/22/2009)
GilaMonster (6/22/2009)
Actually, for SQL 2005 the easiest way is to use sys.tables.
SELECT COUNT(*) FROM sys.tables
Which is fine, as long as you remember that it also includes the occasional system table.

It doesn't contain system tables. The definition of sys.tables (it's a view) is based on a hidden version of sysobjects and contains a filter for only user tables.

CREATE VIEW sys.tables AS

SELECT <lots of columns>

FROM sys.objects$ o

LEFT JOIN < other tables >

WHERE o.type = 'U'

So it contains the same tables as this query

SELECT ... FROM sys.objects o WHERE o.type = 'U'

Now there are replication tables that aren't classified as system, they'll appear in both sys.tables and in a query on sys.objects that filters for type = user tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RichB SSCrazy Eights Points: 9651 More actions · Answer 7

RichB

SSCrazy Eights

Points: 9651

June 22, 2009 at 5:12 am

#1013219

and the ever irritating dtproperties.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

RichardB (6/22/2009)
and the ever irritating dtproperties.

Oh, right, the database diagrams table. I believe to get rid of those you need to filter using ObjectProperty with the IsMSShipped property. I think that gets rid of those.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akash.suryan Mr or Mrs. 500 Points: 533 More actions · Answer 9

select count (*) from sys.sysobjects where xtype ='U'

RichB SSCrazy Eights Points: 9651 More actions · Answer 10

GilaMonster (6/22/2009)
RichardB (6/22/2009)
and the ever irritating dtproperties.
Oh, right, the database diagrams table. I believe to get rid of those you need to filter using ObjectProperty with the IsMSShipped property. I think that gets rid of those.

Interesting. Thanks.

TravisDBA SSCoach Points: 15780 More actions · Answer 11

Yep, and this will work just as well..in SQL 2000,2005, & 2008

SELECT count(*) FROM sysobjects

WHERE type = 'U' AND name NOT LIKE 'dt%'

GO

However, I must say that this is a much more useful query for me about my user tables, and it does not report on MSIsShipped tables or sysdiagrams either and the largest tables float to the top of the list:

SELECT object_name(id) ,rowcnt ,dpages AS [Data Pages], dpages * 8 AS KBytes

FROM sysindexes

WHERE indid IN (1,0)

AND OBJECTPROPERTY(id, 'IsUserTable') = 1

AND object_name(id) != 'sysdiagrams'

ORDER BY rowcnt DESC

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"