SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 New rev of sp_bigtables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 03/04/2006 :  20:57:09  Show Profile  Send aiken an ICQ Message  Reply with Quote
Here's a new version of sp_bigtables that breaks things down by data, text, and indexes. We're looking at getting an ssd (drool), and needed to size it appropriately (no sense putting text on it).

Also, in addition to detailed info on the 25 largest tables, it gives a total resultset for all tables in the db.

Cheers
-b

CREATE PROCEDURE sp_bigtables AS
set nocount on

/**************************************************************************************
*
*  BigTables.sql
*  Bill Graziano (SQLTeam.com)
*  graz@sqlteam.com
*  v1.12
*
*  Updated by aiken to show blob size separately
*
**************************************************************************************/
declare @id	int			
declare @type	character(2) 		
declare	@pages	int			
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)

create table #spt_space
(
	objid		int null,
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	text		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select	id
from	sysobjects
where	xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
	/* Code from sp_spaceused */
	insert into #spt_space (objid, reserved)
		select objid = @id, sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id
	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	update #spt_space
		set data = @pages
	where objid = @id

	select @pages = sum(reserved)
			from sysindexes
				where indid = 255
					and id = @id

	update #spt_space
		set text = @pages
	where objid = @id


	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
				where indid in (0, 1,255)
				and id = @id)
			    - (data + text)
		where objid = @id

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
						and id = @id)
		where objid = @id
	update #spt_space
		set rows = i.rows
			from sysindexes i
				where i.indid < 2
				and i.id = @id
				and objid = @id
	fetch next from c_tables
	into @id
end

select top 25
	Table_Name = (select left(name,25) from sysobjects where id = objid),
	rows = convert(char(11), rows),
	reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
	data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
	text_KB = ltrim(str(text * d.low / 1024.,15,0) + ' ' + 'KB'),
	index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
	unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB'),
        idx_data_ratio = ltrim(str(indexp*100 /data) + '%'),
        unused_pct = ltrim(str(unused * 100 /reserved) + '%')
		
from 	#spt_space, master.dbo.spt_values d
where 	d.number = 1
	and 	d.type = 'E'
	and	rows>0
order by reserved desc

select Table_Name = 'Total',
	rows = convert(char(11), sum(rows)),
	reserved_KB = ltrim(str(sum(reserved) * d.low / 1024.,15,0) + ' ' + 'KB'),
	data_KB = ltrim(str(sum(data) * d.low / 1024.,15,0) + ' ' + 'KB'),
	text_KB = ltrim(str(sum(text) * d.low / 1024.,15,0) + ' ' + 'KB'),
	index_size_KB = ltrim(str(sum(indexp) * d.low / 1024.,15,0) + ' ' + 'KB'),
	unused_KB = ltrim(str(sum(unused) * d.low / 1024.,15,0) + ' ' + 'KB'),
        idx_data_ratio = ltrim(str(sum(indexp)*100 /sum(data)) + '%'),
        unused_pct = ltrim(str(sum(unused) * 100 /sum(reserved)) + '%')
from 	#spt_space, master.dbo.spt_values d

where 	d.number = 1
	and 	d.type = 'E'
	and	rows>0
group by d.low


drop table #spt_space
close c_tables
deallocate c_tables
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000