Table Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Count

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2008-11-10 : 09:31:57

Does anyone know how to do a table count in SQL? The database I am working with has grown substantially over the past month and I need a way to document database size.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-10 : 09:39:01

do u want to count tables in your database or records in each table ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-10 : 09:46:57

you can count tables and other objects from SYS.OBJECTS

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2008-11-10 : 10:16:20

I want to count tables only. How do I get into SYS.OBJECTS? I am new to this.....Would you happen to know if there is a tool in SQL to build a database schema?

quote:
Originally posted by lionofdezert

you can count tables and other objects from SYS.OBJECTS

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 10:56:03

select count(*) from sys.objects where type ='U' and name not like 'Sy%'

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-10 : 11:06:30

select * from sys.tables where is_ms_shipped='0'

malay

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-10 : 11:09:22

u can count system tables as well as user tables

select count(*) from sys.tables where is_ms_shipped='0' --for user objects

select count(*) from sys.tables where is_ms_shipped='1' --for system objects

malay

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2008-11-10 : 11:19:19

Thankyou, malay. This worked perfect.

quote:
Originally posted by malaytech2008

u can count system tables as well as user tables

select count(*) from sys.tables where is_ms_shipped='0' --for user objects

select count(*) from sys.tables where is_ms_shipped='1' --for system objects

malay

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-10 : 11:26:06

You can do this via three ways i would use

by the system tables

SELECT
COUNT(*)
FROM
sys.objects
WHERE
type = 'U'

SELECT
count(*)
FROM
sys.tables
WHERE
TYPE = 'U'

or a safer way where you need no where clauses and which is recommended as its not recommended to query the system table anyway is

SELECT
Count(*)
FROM
INFORMATION_SCHEMA.TABLES

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-10 : 16:42:59

quote:
Originally posted by jcb267

Does anyone know how to do a table count in SQL? The database I am working with has grown substantially over the past month and I need a way to document database size.

While the posted scripts will give you table counts, how would that represent growth? # of tables <> growth but # of tables + row counts would give a little more credibility to what you may consider growth. A sum of (# of row counts (by table) * row size) would be even more accurate if looking at overall growth. Personally, I take a snapshot of each table, the row count and total table size each week so I have historical information to look at, which allows me to project growth reasonably well (knowing full well that there are occasions of a mass data load/delete or the like). I downloaded the following code back in February, which looks at overall DB size, for SQL 2005 (I am not running it yet but anticipate it in the near future). I wish I could give the author or site it's due credit but I don't remember where I found it. (I also downloaded a 2000 version that I do run every week). Maybe this'll help even further (a combination of the 2, perhaps?) than what's already been supplied (and those replies do answer your exact question).

--PART 1
If exists (Select name from sys.objects where name = 'DBGrowthRate' and Type = 'U')
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize decimal(10,2), CurSize decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

Drop table #TempDBSize

Select *
from DBGrowthRate
--Above creates initial table and checks initial data

--PART 2
--Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
- dgr.CurSize)) + ' MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End

--Select *
--from DBGrowthRate
----Verifies values were entered

Drop table #TempDBSize2

Terry

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 01:40:14

quote:
Originally posted by sodeep

select count(*) from sys.objects where type ='U' and name not like 'Sy%'

I dont think you need this condition
name not like 'Sy%'

what happens if user defined table starts with Sy?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 01:43:07

quote:
Originally posted by NeilG

You can do this via three ways i would use

by the system tables

SELECT
COUNT(*)
FROM
sys.objects
WHERE
type = 'U'

SELECT
count(*)
FROM
sys.tables
WHERE
TYPE = 'U'

or a safer way where you need no where clauses and which is recommended as its not recommended to query the system table anyway is

SELECT
Count(*)
FROM
INFORMATION_SCHEMA.TABLES

select count(*) from information_schema.tables
where table_type='BASE TABLE'
and table_name<>'dtproperties'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 02:01:10

quote:
Originally posted by madhivanan

quote:
Originally posted by NeilG

You can do this via three ways i would use

by the system tables

SELECT
COUNT(*)
FROM
sys.objects
WHERE
type = 'U'

SELECT
count(*)
FROM
sys.tables
WHERE
TYPE = 'U'

or a safer way where you need no where clauses and which is recommended as its not recommended to query the system table anyway is

SELECT
Count(*)
FROM
INFORMATION_SCHEMA.TABLES

select count(*) from information_schema.tables
where table_type='BASE TABLE'
and table_name<>'dtproperties'

Madhivanan

Failing to plan is Planning to fail

will this exclude all system tables?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 03:07:05

Yes

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 03:19:31

quote:
Originally posted by madhivanan

Yes

Madhivanan

Failing to plan is Planning to fail

but this included lots of system table when i ran it in master

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-11 : 03:22:40

quote:
Originally posted by madhivanan
select count(*) from information_schema.tables
where table_type='BASE TABLE'
and table_name<>'dtproperties'

Madhivanan

Failing to plan is Planning to fail

But this is not excluding the system tables.

malay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources