Querying the max row length
-
Hello everybody,
I just started at a new customer site and discovered that they have lots of tables the the theoretical total lentgh of a record can be over 8060 bytes.
Some tables have up to 8 nvarchar(2000) columns. So far this seems not to have caused any issues but I'd like to do a check on all these tables what is the actual length of the records and especially what is the max length. Since we're talking about more than 200 tables I'm looking for a script to automate this task.
If anyone has such a script please make my day.
TIA
Markus
[font="Verdana"]Markus Bohse[/font]
-
here you go; a snippet i made a while back:
create table ##tmp (TableName varchar(100),DefinedRowSize int)
exec sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '
select * from ##tmp order by DefinedRowSize desc
if you have a tablename bigger than 100 chars, you'll need to change the varchar(100) to avoide error "string or binary would be truncated"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
thanks for your response, but thats not what I'm looking for. I already have a script which calculates the defined rowlength. What I now try to find out is the max actual length used.
I found a script using the DATALENGTH function which gives me the max length for each column, but I still need to find a way to add up all the columns and then find records which are close to the 8060 bytes limit.
Markus
[font="Verdana"]Markus Bohse[/font]
-
ok how about this:
it gives results that look something like this:
there is also an underlying table ##tmp, where you can see which columsn are defined but never used
Table DefinedLength MaxActualLength VVVREPORTS 7193 112 WWW_QUARTERLY_0506_VS_GOALS_JEREMIAH 4138 1540 WWW_PERFORMANCE_VS_GOALS_APRIL_2006 4048 973 VVVREPORTUSERS 3633 0 GMPARAM 2892 136 TBSCHTSK 2754 0 select sysobjects.name as TableName,
syscolumns.name as ColumnName,
TYPE_NAME(syscolumns.xtype) AS VariableType,
syscolumns.length AS DefinedLength,
0 as MaxActualLength
into ##tmp
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
declare
@isql varchar(2000),
@tbname varchar(64),
@clname varchar(64)
declare c1 cursor for
select TableName,ColumnName from ##tmp where VariableType in('varchar','char','nvarchar','nchar')
open c1
fetch next from c1 into @tbname,@clname
While @@fetch_status <> -1
begin
select @isql = 'UPDATE ##TMP SET MaxActualLength = (SELECT ISNULL(max(DATALENGTH(@clname)),0) FROM @tbname) WHERE TableName =''@tbname'' and ColumnName =''@clname'''
select @isql = replace(@isql,'@tbname',@tbname)
select @isql = replace(@isql,'@clname',@clname)
print @isql
exec(@isql)
fetch next from c1 into @tbname,@clname
end
close c1
deallocate c1
select TableName,sum(DefinedLength) As DefinedLength,sum(MaxActualLength) As MaxActualLength
from ##tmp
group by TableName
order by sum(DefinedLength)DESC,sum(MaxActualLength) DESC
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Nicely done, Lowell... you might want to check the ##Tmp table, though... the numeric datatypes always end up having a MaxActualLength of "0" and I think that's probably not right.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff; I'm sure you are right...I should initialize the MaxActualLength with the values from syscolumns, and then update...makes the comparison much better.
You contribute a lot to SSC; thanks!
I also found it might fail if sometables or columns are poorly named...In one database, a column was named "Function", and that would make the cursor's ISNULL(max(DATALENGTH([@clname])),0) section throw an error, so I added brackets as well
here's the slightly better version.
select sysobjects.name as TableName,
syscolumns.name as ColumnName,
TYPE_NAME(syscolumns.xtype) AS VariableType,
syscolumns.length AS DefinedLength,
syscolumns.length AS MaxActualLength
into ##tmp
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
declare
@isql varchar(2000),
@tbname varchar(64),
@clname varchar(64)
declare c1 cursor for
select TableName,ColumnName from ##tmp where VariableType in('varchar','char','nvarchar','nchar')
open c1
fetch next from c1 into @tbname,@clname
While @@fetch_status <> -1
begin
select @isql = 'UPDATE ##TMP SET MaxActualLength = (SELECT ISNULL(max(DATALENGTH([@clname])),0) FROM [@tbname]) WHERE TableName =''@tbname'' and ColumnName =''@clname'''
select @isql = replace(@isql,'@tbname',@tbname)
select @isql = replace(@isql,'@clname',@clname)
print @isql
exec(@isql)
fetch next from c1 into @tbname,@clname
end
close c1
deallocate c1
select TableName,sum(DefinedLength) As DefinedLength,sum(MaxActualLength) As MaxActualLength
from ##tmp
group by TableName
order by sum(DefinedLength)DESC,sum(MaxActualLength) DESC
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Ya do pretty darned well yourself, Lowell!
You made a very useful tool. I'm in the process of tweaking it for my own use and thought I'd share it with you. I changed your formula a bit so that if a column contains no data, it's length is returned as NULL (Unused Column) and added the brackets you suggested.I've got a couple of other things I think I'll try adding, but here's the code as it currently stands and thanks again...
--===== Setup the environment SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON SET ANSI_WARNINGS OFF
--===== If the result table already exists, drop it IF OBJECT_ID('TempDB..#ColumnData') IS NOT NULL DROP TABLE #ColumnData--===== Declare the local variables DECLARE @Columns INT --Total number of columns found DECLARE @Counter INT --General purpose loop counter DECLARE @SQL VARCHAR(600) --Contains the dynamic SQL for each column
--===== Populate the result table with the initial table/column info SELECT RowNum = IDENTITY(INT,1,1), TableName = OBJECT_NAME(sc.ID), ColumnName = sc.Name, DataType = UPPER(TYPE_NAME(sc.XType)), DefinedLength = sc.Length, MaxActualDataLength = CAST(NULL AS INT) INTO #ColumnData FROM dbo.SysColumns sc WHERE OBJECTPROPERTY(sc.ID,'IsTable') = 1 AND OBJECTPROPERTY(sc.ID,'IsMSShipped') = 0
--===== Remember how many columns there are SET @Columns = @@ROWCOUNT
--===== Add a primary key to the result table (just 'cuz) ALTER TABLE #ColumnData ADD PRIMARY KEY CLUSTERED (ROWNUM) WITH FILLFACTOR = 100
--===== Loop through the column data and find the actual max data length for each SET @Counter = 1 WHILE @Counter <= @Columns BEGIN SELECT @SQL = 'UPDATE #ColumnData SET ' + 'MaxActualDataLength=(SELECT MAX(DATALENGTH(['+ColumnName+'])) FROM ['+TableName+'])' + 'WHERE RowNum='+CAST(@Counter AS VARCHAR(10)) FROM #ColumnData WHERE RowNum = @Counter -- PRINT @SQL EXEC (@SQL) SET @Counter = @Counter+1 END
--===== Display the columns not fully utilized in order of worst usage of the length -- Note that NULL columns contain no data at all. -- Note that this does NOT find columns that have been RPadded to max length (yet). SELECT *,DefinedLength-MaxActualDataLength AS MinUnused FROM #ColumnData WHERE ISNULL(MaxActualDataLength,0)<DefinedLength ORDER BY CASE WHEN MaxActualDataLength IS NULL THEN 9999 ELSE DefinedLength-MaxActualDataLength END DESC,TableName,ColumnName
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello Lowell and Jeff,
thank you both for your efforts.
Even though it's still not exactly what I had in mind, I think I can work with your scripts.
Markus
[font="Verdana"]Markus Bohse[/font]
-
Nice Jeff; your version does a much better job of analysis and presenting a basisi for improvements to someone's schema, i like it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Maybe in the last SELECT but the rest of it is based almost exactly on what you did, Lowell. I just changed it a bit just 'cause I like changing stuff
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Markus... but tell us... what did you have in mind? Both scripts return the necessary information (we thought) you were requesting... what's missing?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
what I was hoping to find was a script which gives the the max actual value for the whole row. The idea was to find rows which could cause problems the next time someone tries to update them.
Your script tells me for each column, which row is the longest, but not the total for the row.
But by just concentrating on the large (n)varchar columns I should be able to find any rows which might be close to the 8060 limit.
Thanks again for all your efforts
Markus
[font="Verdana"]Markus Bohse[/font]
-
the script does...just look for anything with an DefinedSize greater than 8060:
create table BadExample(
varint int,
var1 varchar(8000),
var2 varchar(8000),
var3 varchar(8000),
var4 varchar(8000))
insert into BadExample(varint,var1,var2,var3,var4) values (1,'sometext','more text than before','etc,etc',null)
you get this on creation and insert:
Warning: The table 'BadExample' has been created but its maximum row size (32033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
(1 row(s) affected)
Warning: The table 'BadExample' has been created but its maximum row size (32033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
and the script produces the results you'd expect, how big it's defined,a dn how much you could potentially shrink it based on the data in the table.
TableName DefinedLength MaxActualLength BadExample 32004 40 TBSCHTSK 2754 44 PPNCSHATAX 2553 1328 Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Oops,
sorry Lowell my comment was about Jeff's script. I must admit I was in a hurry yesterday and didn't take the time to test all the scripts. So thank you even more, your script is exactly what I actually asked for.
But having Jeff's script aside is also very usefull since he breaks down the shrinking potential for each column.
Markus
[font="Verdana"]Markus Bohse[/font]
-
The neat think about counting Pennies is that the Dollars take care of themselves
Add the following to the end of my script...SELECT TableName,
SUM(DefinedLength) AS DefinedRowWidth,
SUM(DefinedLength-MaxActualDataLength) AS UsedRowWidth
FROM #ColumnData
GROUP BY TableName
HAVING SUM(DefinedLength) > 8060
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply