?
Solved

Directory and statistics of each table in an SQL 2000 database

Posted on 2011-02-22
5
Medium Priority
?
392 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 100 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 100 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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