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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply