question

Naina avatar image
Naina asked

Total number Tables, their row count and their column count from a database

Hello,

Good Morning Every One

Please Let me know how to get the Total number Tables, their row count and their column count from a database.

Also the script should work on both SQL Server 2000 and SQL Server 2008.

Thanks Naina

sqlstatistics
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Naina avatar image Naina commented ·
Hello Guys, Its bit urgent... Please let me knw if anyone got the answer for this. Thanks naina
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

These queries will get you the details but be very careful running them on a table with a large row count, they could take a while. There are better ways to do this but you said you are in a hurry . . .

SELECT  COUNT(*) AS [table count]
FROM    [sys].[tables] AS t

SELECT  COUNT(*) AS [COLUMN COUNT],
        [c].object_id
FROM    [sys].[tables] AS t
        INNER JOIN [sys].[columns] AS c ON [c].[object_id] = [t].[object_id]
GROUP BY [c].[object_id]

EXEC [sys].[sp_MSforeachtable] @command1 = 'select ''?'',count(*) as [row count] from ?  with (nolock)'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

+1 to Fatherjack - but I would count the rows differently (formatting won't fit in a comment)

For 2005/8:

SELECT OBJECT_NAME([object_id]), SUM([row_count]) 
  FROM [sys].[dm_db_partition_stats] 
 WHERE [index_id] IN (0,1)
 GROUP BY [object_id]

For 2000:

SELECT OBJECT_NAME([id]), SUM([rowcnt]) 
  FROM [sysindexes] 
 WHERE [indid] IN (0,1)
 GROUP BY [id]

These methods will give you the row count instantly - however the 2000 version might be a bit out, as it didn't do a very good job of keeping track of row counts.

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - nice. was worried about HEAPS and forgot they are ind id 0!!! Dont you need the tables in there to get a count for each .. SELECT SUM([rowcnt]), [t].name FROM [sysindexes] AS s INNER JOIN [sys].[tables] AS t ON [s].[id] = [t].[object_id] WHERE [indid] IN ( 0, 1 ) GROUP BY [t].[name] ORDER BY [t].[name] ?? Just a thought
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Yeah, thinking about it that will give you rows from indexed views too - good spot...
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Ha - 12 hours later he finally realises what Fatherjack was on about. Slooooooow day, apologies sir. Editing now.
0 Likes 0 ·
Naina avatar image
Naina answered

Hello Fatherjack n Matt Whitfield,

Thanks a lot for ur help.

I want result from a particular Database . It should be like :-

Suppose :

Database : X

Tablename : Customer

RowCount : 100

ColumnCount :5

Thanks Naina

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Then you can write some code which formats the output as you require it...
0 Likes 0 ·
Oleg avatar image
Oleg answered

This is a complete statement to return data as laid out in Naina's answer/request (returning database name, table name, rowcount, columncount). The following code will work in 2005/2008. In earlier versions the views used in the query will need to be replaced with corresponding system tables minus the partitions as the sysindexes already includes the rowcnt column:

select
    db_name() DatabaseName, 
    s.name + '.' + o.name TableName,
    sum(p.rows) RecordCount, count(c.column_id) ColumnCount
    from sys.indexes i inner join sys.partitions p
        on i.[object_id] = p.[object_id] and i.index_id = p.index_id
    inner join sys.objects o
        on o.[object_id] = i.[object_id]
    inner join sys.columns c
        on o.[object_id] = c.[object_id]
    inner join sys.schemas s
        on o.[schema_id] = s.[schema_id] 
    where i.index_id < 2
        --and s.name in ('dbo', 'your_other_schema')
        and o.type = 'U'
    group by s.name, o.name
    order by s.name, o.name;

Uncomment and modify the and s.name in line to order to select data only for specified schemas (if needed)

Here is the script which will work in SQL Server 2000:

select
    u.name + '.' + o.name TableName,
    sum(i.rows) RecordCount, count(c.id) ColumnCount
    from dbo.sysindexes i
    inner join dbo.sysobjects o
        on i.[id] = o.[id]
    inner join dbo.syscolumns c
        on o.[id] = c.[id]
    inner join dbo.sysusers u
        on o.[uid] = u.[uid] 
    where i.indid < 2
        --and u.name in ('dbo')
        and o.type = 'U'
    group by u.name, o.name
    order by u.name, o.name;

Matt has pointed out in his answer that the

2000 version might be a bit out, as it didn't do a very good job of keeping track of row counts.

Therefore, it is best to avoid using the script referencing the older system tables directly with newer version of the database. In other words, do use the first script for SQL Server 2005/2008 and use the second script for SQL Server 2000.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.