Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I found this script sql-server-2005-reaching-table-row-size-limit that seems to return the row size per defined data type lengths. I need a script that would give me all the rows in a table that their max data size is over the recommended 8024 (whatever MS recommends)

share|improve this question
You can try using msdn.microsoft.com/en-us/library/ms188917%28v=sql.105%29.aspx - SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'Database_Name'), OBJECT_ID(N'Table_Name'), NULL, NULL, 'DETAILED') and look for anything where the alloc_unit_type_desc is ROW_OVERFLOW_DATA – dash Oct 5 '12 at 10:56
MS SQL server allows only 8060bytes of data max to be stored in a row.Hence your row size will alwayas be <= 8060.It will never cross this. – AnandPhadke Oct 5 '12 at 11:01
1  
@AnandPhadke This is not totally right: msdn.microsoft.com/en-us/library/ms186981%28SQL.90%29.aspx – Jaime Oct 5 '12 at 11:24

migrated from stackoverflow.com Oct 6 '12 at 6:56

2 Answers

up vote 1 down vote

Try this script:

declare @table nvarchar(20)
declare @idcol nvarchar(50)
declare @sql nvarchar(max)

--initialize those two values
set @table = 'YourTable'
set @idcol = 'some id to recognize the row'

set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength(' + name + '), 1)' 
        from sys.columns where object_id = object_id(@table)
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'

PRINT @sql

exec (@sql)

The rows will be ordered by size, so you can check from top to down.

share|improve this answer
yeah its not applies for varchar I agree.Here above your query is not covering all the columns of a table – AnandPhadke Oct 5 '12 at 11:41
@AnandPhadke What columns is not covering? Thanks – Jaime Oct 5 '12 at 12:42
up vote 0 down vote

try this:

;WITH CTE as(select *,LEN(ISNULL(col1,''))+LEN(ISNULL(col2,'')) as row_len from yourtable)
select * from CTE where row_len > 8060
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.