• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Directory and statistics of each table in an SQL 2000 database

I am using SQL2000 for my database, which has 178 tables and 23 GB in MDF size. (including 30+ system files).

I wish to determine a quick way to know how big is each table and how many rows it has, how much hard disk space it is taking. May I ask how to find that out?

Thank you.
0
cq27
Asked:
cq27
2 Solutions
 
dbaSQLCommented:
Use this to find the basic details regarding your table size:

exec sp_spaceused 'tablename'

use this to find the rowcount per table:

DECLARE @SQL NVARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC sp_executeSQL @SQL

SELECT
      Schema_name(t.schema_id) AS SchemaName,
  t.name                   AS TableName,
  i.rows                   as [Rows]
FROM  
      sys.tables AS t INNER JOIN sys.sysindexes AS i
    ON t.object_id = i.id AND i.indid < 2
ORDER BY
      t.name

GO
0
 
cq27Author Commented:
Hi there

For the table size query "exec sp_spaceused 'tablename'", is there a way to display all the user tables at a go? Otherwise it will be a tedious process to check every single table out.

For the rowcount per table, when I ran this query, I receive this:

'Schema_name' is not a recognized function name.

Could you assist in this? Thank you.
0
 
John ClaesCommented:
Hey,

I'm always using the folowing script :


This Script returns  :
Number of Rows  / Reserved space / Data Size / Index Size / Unused Size

The reserved Size is the Total Size of the Table ;-)

regards

poor beggar
CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)      --For storing values in the cursor
DECLARE @TableSchema VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select sysobjects.[name],sys.schemas.[name]
from sysobjects
INNER JOIN sys.schemas ON sysobjects.uid  = sys.schemas.schema_id 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @TableSchema

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
	declare @TableFullName varchar(201)
	set @TableFullName= @TableSchema + '.' + @TableName
print @TableFullName
    INSERT  #TempTable
        EXEC sp_spaceused @TableFullName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @TableSchema
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT * 
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO

Open in new window

0
 
dbaSQLCommented:
>>is there a way to display all the user tables at a go?
Yes, do this:

USE yourdatabasename
GO

EXEC sp_msforeachtable 'exec sp_spaceused [?]'
GO

The error on schema_name is likely because you're on v2000.  I missed that.  You can try either of these items, though the first is not guaranteed to be accurate.  I'd use the second one.
#1
SELECT SO.Name, SI.rows 
FROM sysindexes SI, 
    SysObjects SO 
WHERE SI.id = SO.ID 
    AND SI.indid < 2
    AND SO.Type = 'U'
order by so.name


#2
create table tablecounts (
  tablename varchar(255) not null,
  recordcount int not null
)
go

exec sp_msforeachtable "insert into tablecounts select '?' as tablename, count(*) from ?"

select * from tablecounts
drop table tablecounts

Open in new window

0
 
Anthony PerkinsCommented:
Or you can simply do this from EM:
1. Right-click on the database.
2. Select View.
3. Select Taskpad.
4. Click on the Table Info tab.

You will get for each table the following information:
Rows
Size
Index Size
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now