Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 11792
Home > Forums > Sql Server >

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

Posted by Cnagasa under Sql Server on 2/9/2012 | Points: 10 | Views : 38105 | Status : [Member] | Replies : 12
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
hi every one,

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

sudarshan.c

Reply | Reply with Attachment
Alert Moderator 

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

Latest Posts