Solved

calculate space used by each table in a database

Posted on 2002-05-01
5
674 Views
Last Modified: 2011-09-20
How to find out space used by each table in a database.
I know we can use sp_spaceused "tablename" but this gives for only one table at a time is there any way to get for all the tables at a time.

Thanks,

hmk
0
Comment
Question by:hmk
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6984661
hmk,

you can use the following query to get information about all the tables in the database ( I just extracted this piece of code from sp_spaceused :-) Please let me know if it solves your purpose.

You can even convert this into a stored proc.

Thanks.

                select  name = o.name,
                        iname = i.name,
                        low = d.low,
                        rowtotal = rowcnt(i.doampg),
                        reserved = convert(numeric(20, 9),
                                (reserved_pgs(i.id, i.doampg) +
                                reserved_pgs(i.id, i.ioampg))),
                        data = convert(numeric(20, 9), data_pgs(i.id, i.doampg)),
                        index_size =  convert(numeric(20, 9),
                                data_pgs(i.id, i.ioampg)),
                        unused = convert(numeric(20, 9),
                                ((reserved_pgs(i.id, i.doampg) +
                                reserved_pgs(i.id, i.ioampg)) -
                                (data_pgs(i.id, i.doampg) +
                                data_pgs(i.id, i.ioampg))))
                into #pagecounts
                from sysobjects o, sysindexes i, master.dbo.spt_values d
                                where i.id = o.id
                                        and o.type = "U"
                                        and d.number = 1
                                        and d.type = "E"
 
  select distinct name,
                    rowtotal = convert(char(11), sum(rowtotal)),
                    reserved = convert(char(15), convert(varchar(11),
                               convert(numeric(11, 0), sum(reserved) *
                               (low / 1024))) + " " + "KB"),
                    data = convert(char(15), convert(varchar(11),
                               convert(numeric(11, 0), sum(data) * (low / 1024)))
                               + " " + "KB"),
                    index_size = convert(char(15), convert(varchar(11),
                                convert(numeric(11, 0), sum(index_size) *
                                (low / 1024))) + " " + "KB"),
                    unused = convert(char(15), convert(varchar(11),
                                convert(numeric(11, 0), sum(unused) *
                                (low / 1024))) + " " + "KB")
                from #pagecounts
 
group by name
order by 1
 
drop table #pagecounts
0
 

Author Comment

by:hmk
ID: 6986083
Excellent, I got it.
But I have one more question. How to get total space used/allocated/free for the database in the same above statements. If we sum up data+index_size gives the total space used by database.

Thanks,

hmk
0
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 100 total points
ID: 6986756
here is what is want..

 
set nocount on
 
               select  name = o.name,
                       iname = i.name,
                       low = d.low,
                       rowtotal = rowcnt(i.doampg),
                       reserved = convert(numeric(20, 9),
                               (reserved_pgs(i.id, i.doampg) +
                               reserved_pgs(i.id, i.ioampg))),
                       data = convert(numeric(20, 9), data_pgs(i.id, i.doampg)),
                       index_size =  convert(numeric(20, 9),
                               data_pgs(i.id, i.ioampg)),
                       unused = convert(numeric(20, 9),
                               ((reserved_pgs(i.id, i.doampg) +
                               reserved_pgs(i.id, i.ioampg)) -
                               (data_pgs(i.id, i.doampg) +
                               data_pgs(i.id, i.ioampg))))
               into #a
               from sysobjects o, sysindexes i, master.dbo.spt_values d
                               where i.id = o.id
                                       and o.type = "U"
                                       and d.number = 1
                                       and d.type = "E"
 
 select distinct name,
                   rowtotal = sum(rowtotal),
                   reserved = convert(numeric(11, 0), sum(reserved) * (low / 1024)),
                   data     = convert(numeric(11, 0), sum(data) * (low / 1024)) ,
                   index_size = convert(numeric(11, 0), sum(index_size) * (low / 1024)),
                   unused = convert(numeric(11, 0), sum(unused) * (low / 1024))
into #y
               from #a
group by name
 
 select distinct name,
                   rowtotal = convert(char(11), sum(rowtotal)),
                   reserved = convert(char(15), convert(varchar(11),
                              convert(numeric(11, 0), sum(reserved) *
                              (low / 1024))) + " " + "KB"),
                   data = convert(char(15), convert(varchar(11),
                              convert(numeric(11, 0), sum(data) * (low / 1024)))
                              + " " + "KB"),
                   index_size = convert(char(15), convert(varchar(11),
                               convert(numeric(11, 0), sum(index_size) *
                               (low / 1024))) + " " + "KB"),
                   unused = convert(char(15), convert(varchar(11),
                               convert(numeric(11, 0), sum(unused) *
                               (low / 1024))) + " " + "KB")
               from #a
group by name
order by 1
 
select
                   reserved = sum(reserved),
                   data =  sum(data) ,
                   index_size = sum(index_size) ,
                   unused = sum(unused)
from #y
0
 

Author Comment

by:hmk
ID: 6986888
amit, i posted one more question put these statements in a procedure(which will stay in one database and pass data base name as parameter to get table info in other database) and I don't know how to use use database in a procedure.
I hope u have solution for this.

Thanks,

hmk  
0
 

Expert Comment

by:JohnCoupe
ID: 12729379
System stored procedures, SSPs, always execute within the context of the database from which they are executed. The easy fix would be to put "use" statements inside the SSP but this isn't allowed. However, you can execute an SSP within the context of another database by prefixing the SSP with the target database name and "..". For example, the following SSP simply returns the current database name:

USE sybsystemprocs
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp_what_is_my_name') and type = 'P')
DROP PROCEDURE sp_what_is_my_name
GO

CREATE PROCEDURE sp_what_is_my_name
AS
SELECT db_name()
GO

E.g.
SET NOCOUNT ON
USE master
GO
EXEC sp_what_is_my_name
GO

 ------------------------------
 master
(return status = 0)

To run it in a different database context then use:
EXEC tempdb..sp_what_is_my_name
GO

 ------------------------------
 tempdb
(return status = 0)


The next example SSP uses this context switching facility to run the above SSP in each database:

USE sybsystemprocs
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp_what_are_my_names') and type = 'P')
DROP PROCEDURE sp_what_are_my_names
GO

CREATE PROCEDURE sp_what_are_my_names
@dbname_like varchar(50) = "%"
AS
DECLARE database_cur CURSOR FOR
SELECT d.name
FROM   master.dbo.sysdatabases d
WHERE  d.name LIKE @dbname_like
FOR READ ONLY
DECLARE @name VARCHAR(30)
DECLARE @sql  VARCHAR(50)

OPEN database_cur
FETCH database_cur INTO @name
WHILE (@@sqlstatus = 0)
BEGIN
  SELECT @sql = @name + '..sp_what_is_my_name'
  EXEC @sql
  FETCH database_cur INTO @name
END
CLOSE database_cur
DEALLOCATE CURSOR database_cur
GO

EXEC sp_what_are_my_names
GO

 ------------------------------
 master

 ------------------------------
 model

 ------------------------------
 sybsystemdb

 ------------------------------
 sybsystemprocs

 ------------------------------
 tempdb
(return status = 0)

EXEC sp_what_are_my_names '%db'
GO

 ------------------------------
 sybsystemdb

 ------------------------------
 tempdb
(return status = 0)


So, to answer your original question simply replace sp_what_is_my_name in the above SSP with the name of your procedure and you can find out how much space is used in any number of databases. An alternative solution is to use dynamic SQL in the originally posted solution but I think that it's far easier to read and maintain if you have two fairly simple SSPs rather than one quite complicated one. Also, the above method works in more versions of Sybase since dynamic SQL only became available relatively recently.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase ASE 15.7 do not know datepart function 2 1,205
SyBase SQL Syntax 5 638
StorageCraft ShadowProtect Sybase VSS? 3 583
sybase space calculatio, why must divide / multiply by 512 ? 3 126
A customer recently asked me about anti-malware and the different deployment options available for his business. Daily news about cyberattacks, zero-day vulnerabilities, and companies that suffered a security breach made him wonder if the endpoint a…
An analysis of the phishing scam that has been affecting Google users, along with steps to take for protection, as well as what to do if you receive one of the emails.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

23 Experts available now in Live!

Get 1:1 Help Now