Count No of Table available in Current Database

Posted by Sksamantaray under Sql Server category on | Points: 40 | Views : 1857 count(*) TotalTable FROM DatabaseName.INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE' Count(*) TotalTables from sys.tables

Comments or Responses

Posted by: on: 2/13/2012 Level:Starter | Status: [Member] | Points: 10
ya sir it really y nice
Posted by: PandianS on: 2/14/2012 Level:Silver | Status: [Member] [MVP] | Points: 10

The following script also can be used to findout, No of tables available in ALL the database(s) in active instance
Create Table #TableCount

DBName Varchar(100),
TableCount Int

Insert #TableCount
Exec Sp_MsforeachDB 'Use [?]; Select DB_Name(),Count(1) from sys.all_objects Where type=''U'' And is_ms_shipped =0 Having Count(1) >1'

Select * from #TableCount

Drop Table #TableCount

Login to post response