December 18, 2003 at 9:02 am
I'm looking for a tool that would calculate the min, max and average row size of a table. This tool would perform one or two of these functions:
1) Would use the information_schema.columns information to calculate the max and min row size based on a table definition.
2) Would actually read all the rows in a table and calculate the average row size.
I hate to reinvent the wheel. If someone has this tool I would love a copy. If not I will write it myself.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 18, 2003 at 12:37 pm
quote:
I'm looking for a tool that would calculate the min, max and average row size of a table. This tool would perform one or two of these functions:1) Would use the information_schema.columns information to calculate the max and min row size based on a table definition.
2) Would actually read all the rows in a table and calculate the average row size.
I hate to reinvent the wheel. If someone has this tool I would love a copy. If not I will write it myself.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 18, 2003 at 1:10 pm
Sorry about that blank post. Ok took a stab at creating the Average Records Length query. I use the information in sp_spaceused to calculate the average. Any flaws in my logic for calculating the average record length for each table in a database.
Code below:
----------------------------------------------------------------
-- Display average row size for each table in a database
-- Author: Greg Larsen Date: 12/18/2003
----------------------------------------------------------------
set nocount on
declare @old_tname varchar(100)
declare @avg_row int
declare @tname varchar(100)
declare @data int
declare @row int
create table #size (tname varchar(1024),
row int,
reserve char(18),
data char(18),
index_size char(18),
unused char(18))
create table #results (tname varchar(100),
data_size int,
rows int)
select top 1 @tname=name from sysobjects where xtype = 'u' order by name
set @old_tname = ''
while @old_tname < @tname
begin
insert into #size exec ('sp_spaceused ' + @tname + ',true')
select @row=row, @data=substring(data,1,charindex(' ',data)-1) from #size
insert into #results values (@tname,@data,@row)
set @old_tname = @tname
select top 1 @tname=name from sysobjects where xtype = 'u' and name > @old_tname order by name
end
select left(tname, 45),
rows, data_size,
case rows when 0 then 0
else (1024*cast(data_size as float))/cast(rows as float)
end as avg_row_size,
case rows when 0 then 0
else 8024.0/((1024*cast(data_size as float))/cast(rows as float))
end as num_rows_per_page
from #results
drop table #results
drop table #size
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 18, 2003 at 1:15 pm
oops. found a bug, try this one:
----------------------------------------------------------------
-- Display average row size for each table in a database
-- Author: Greg Larsen Date: 12/18/2003
----------------------------------------------------------------
set nocount on
declare @old_tname varchar(100)
declare @avg_row int
declare @tname varchar(100)
declare @data int
declare @row int
create table #size (tname varchar(1024),
row int,
reserve char(18),
data char(18),
index_size char(18),
unused char(18))
create table #results (tname varchar(100),
data_size int,
rows int)
select top 1 @tname=name from sysobjects where xtype = 'u' order by name
set @old_tname = ''
while @old_tname < @tname
begin
insert into #size exec ('sp_spaceused [' + @tname + '],true')
select @row=row, @data=substring(data,1,charindex(' ',data)-1) from #size
insert into #results values (@tname,@data,@row)
set @old_tname = @tname
select top 1 @tname=name from sysobjects where xtype = 'u' and name > @old_tname order by name
end
select left(tname, 45),
rows, data_size,
case rows when 0 then 0
else (1024*cast(data_size as float))/cast(rows as float)
end as avg_row_size,
case rows when 0 then 0
else 8024.0/((1024*cast(data_size as float))/cast(rows as float))
end as num_rows_per_page
from #results
drop table #results
drop table #size
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 23, 2003 at 4:53 am
quote:
I hate to reinvent the wheel. If someone has this tool I would love a copy. If not I will write it myself.
How about just using DBCC SHOWCONTIG?
DBCC SHOWCONTIG (TABLENAME) WITH TABLERESULTS
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Contributor to Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy