SQL Server 2008 - Get general stats on tables, row counts etc

How can I query or does someone have an SP that will output general stats on a database,
like

Table Name , Row Count  , File Size,, number of transactions  - db file size etc.
thx
LVL 1
JElsterAsked:
Who is Participating?
 
Guru JiConnect With a Mentor Commented:
You can run the following code against any database, will show u entire table structure with
Row Count, Table Name, ModifiedRows, LastUpdated Date,

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and i.status not in (8388704,8388672)
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

Open in new window


In order to get Table size run the following code which shows table size of all the tables in your database

CREATE TABLE #temp
       (
       [name] nvarchar(128),
       [rows] char(11),
       [reserved] varchar(18),
       [data] varchar(18),
       [index_size] varchar(18),
       [unused] varchar(18)
       )
 
INSERT INTO #temp
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?' "
 
SELECT
       [name] AS TableName,
       CAST([rows] AS int) AS NumOfRows,
       CAST(REPLACE([reserved],' KB','') AS int) AS [TotalKB]
FROM
       #temp
ORDER BY 3 DESC
 
SELECT SUM(CAST(REPLACE([reserved],' KB','') AS int)) AS TotalKB_data FROM #temp
 
DROP TABLE #temp

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.