Query to find the number of tables in a DataBase(DB)

Posted by Cnagasa under Sql Server on 2/9/2012 | Points: 10 | Views : 32409 | Status : [Member] | Replies : 12
hi every one,

Query to find the number of tables in a Data Base(DB)
can u send me the Query Pls

sudarshan.c


Responses

Posted by: Pradeepkumar417 on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

You can get table count by using below query.

select count(*) from sys.tables where is_ms_shipped='0'

Thanks & Regards,
Software Engineer,
Pradeep Kumar

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cnagasa on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
hi pradeep,

i am executing this query i will getting the invalid object error is coming

can u help me

Regard's
Sudarshan.C

sudarshan.c

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pradeepkumar417 on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,


you have to select the database name from dropdown list in query analyzer.

Thanks & Regards,
Software Engineer,
Pradeep Kumar

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pradeepkumar417 on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

you can also try this but before executing you have to select db name

SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES

Thanks & Regards,
Software Engineer,
Pradeep Kumar

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CNAGASA on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
hi ,

yes i am selcting the database name from dropdown list in query analyzer

error msg is

Invalid object name 'sys.tables'.

do the need full

Regard's
Sudarshan.C




Regard's
Sudarshan.C

sudarshan.c

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pradeepkumar417 on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,


Try this

SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES

I tested both queries. I didn't get any error.

Thanks & Regards,
Software Engineer,
Pradeep Kumar

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 2/9/2012 [Member] Silver | Points: 25

Up
0
Down
select count(*) TotalTable FROM officeinspection.INFORMATION_SCHEMA.TABLES

where TABLE_TYPE='BASE TABLE'


Hi, To Count no of table of a database you can use the above Query

Thanks,
Sanjay

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Avdesh on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

Thanks Regards
AVDESH

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 2/9/2012 [Member] Bronze | Points: 25

Up
0
Down
Try this..

USE YourDBName

GO
SELECT *
FROM sys.Tables
GO



Thanks and Regards
Akiii

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naraayanan on: 2/9/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
Check this link
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/how-to-count-number-of-tables-in-a-database-136589

Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cnagasa on: 2/10/2012 [Member] Starter | Points: 25

Up
0
Down
hi ,

good morning

thank u for giving reply



sudarshan.c

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
SELECT * FROM INFORMATION_SCHEMA.TABLES


SELECT * FROM SYS.TABLES


SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES

REGARDS


sriram

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response