Solved

Directory and statistics of each table in an SQL 2000 database

Posted on 2011-02-22
5
342 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
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now