Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Top 10 Large tables in the Database

3 posts in General Discussion Last posting was on 2011-12-19 16:48:01.0Z
vnlsk Posted on 2011-12-16 22:59:49.0Z
Sender: 2421.4eebcc99.1804289383@sybase.com
From: vnlsk
Newsgroups: sybase.public.ase.general
Subject: Top 10 Large tables in the Database
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4eebcd65.2466.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 16 Dec 2011 14:59:49 -0800
X-Trace: forums-1-dub 1324076389 172.20.134.41 (16 Dec 2011 14:59:49 -0800)
X-Original-Trace: 16 Dec 2011 14:59:49 -0800, 172.20.134.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30770
Article PK: 73661

Hi,
Anybody,could you please share sp or sql to find top large
tables in sybase 15.0.3 with size in MB and with rowcount?
I explored on web but those sps are not working in Sybase
15.0.3.
Thank you verymuch in advance.

Regards,
vnlsk


"Mark A. Parsons" <iron_horse Posted on 2011-12-17 01:12:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Top 10 Large tables in the Database
References: <4eebcd65.2466.1681692777@sybase.com>
In-Reply-To: <4eebcd65.2466.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eebec8c$1@forums-1-dub>
Date: 16 Dec 2011 17:12:44 -0800
X-Trace: forums-1-dub 1324084364 10.22.241.152 (16 Dec 2011 17:12:44 -0800)
X-Original-Trace: 16 Dec 2011 17:12:44 -0800, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30771
Article PK: 73662

I don't know what sp's (from the web) you're talking about but I'm assuming they were designed for pre-ASE 15 dataservers.

I'd recommend you look at the source code for a Sybase-supplied stored proc that already provides the data you're
looking for albeit at the table and/or index level.

Specifically, take a look at sp_spaceused. If you understand T-SQL you shouldn't have a problem reverse engineering
this proc to come up with some code to do what you want.

On 12/16/2011 17:59, vnlsk wrote:
> Hi,
> Anybody,could you please share sp or sql to find top large
> tables in sybase 15.0.3 with size in MB and with rowcount?
> I explored on web but those sps are not working in Sybase
> 15.0.3.
> Thank you verymuch in advance.
>
> Regards,
> vnlsk


Bret Halford Posted on 2011-12-19 16:48:01.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Top 10 Large tables in the Database
References: <4eebcd65.2466.1681692777@sybase.com>
In-Reply-To: <4eebcd65.2466.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eef6ac1@forums-1-dub>
Date: 19 Dec 2011 08:48:01 -0800
X-Trace: forums-1-dub 1324313281 10.22.241.152 (19 Dec 2011 08:48:01 -0800)
X-Original-Trace: 19 Dec 2011 08:48:01 -0800, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30774
Article PK: 73665


On 12/16/2011 3:59 PM, vnlsk wrote:
> Hi,
> Anybody,could you please share sp or sql to find top large
> tables in sybase 15.0.3 with size in MB and with rowcount?
> I explored on web but those sps are not working in Sybase
> 15.0.3.
> Thank you verymuch in advance.
>
> Regards,
> vnlsk

10 largest tables in a database by rowcount:

select
top 10
name,
row_count(db_id(), id)
from
sysobjects
order by
row_count(db_id(),id) desc

10 largest tables in a database by allocated space:


select
top 10
name,
reserved_pages(db_id(),id)
/(1024.0 / (@@maxpagesize/1024.0) ) as "Allocated MB"
from
sysobjects
order by
reserved_pages(db_id(),id) desc
go

10 largest tables in a database by used allocated space:


select
top 10
name,
used_pages(db_id(),id)
/(1024.0 / (@@maxpagesize/1024.0) ) as "Used MB"
from
sysobjects
order by
used_pages(db_id(),id)(db_id(),id) desc
go