Solved

Directory and statistics of each table in an SQL 2000 database

Posted on 2011-02-22
5
376 Views
Last Modified: 2012-05-11
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
Comment
Question by:cq27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34958227
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
 

Author Comment

by:cq27
ID: 34958433
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
 
LVL 10

Expert Comment

by:John Claes
ID: 34958905
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
 
LVL 17

Accepted Solution

by:
dbaSQL earned 25 total points
ID: 34961567
>>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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 25 total points
ID: 34963501
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick step-by-step overview of installing and configuring Carbonite Server Backup.
While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question