54

I found this script 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 bytes (whatever MS recommends).

0

6 Answers 6

71

Try this script:

declare @table nvarchar(128)
declare @idcol nvarchar(128)
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(' + QUOTENAME(name) + '), 1)' 
        from  sys.columns 
        where object_id = object_id(@table)
        and   is_computed = 0
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. The rowsize column is in bytes. See more info: DATALENGTH()

0
14

I liked the above from Jaime.
I added some square brackets to handle weird column names.

declare @table nvarchar(128)
declare @idcol nvarchar(128)
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)
1
  • 7
    quotename() is the safe way to handle the square brackets
    – Davos
    Jan 25, 2021 at 12:30
9

And I liked the above from Speedcat and extend it to list all Tables with rowcounts and total bytes.

declare @table nvarchar(128)
declare @sql nvarchar(max)
set @sql = ''
DECLARE tableCursor CURSOR FOR  
SELECT name from sys.tables

open tableCursor
fetch next from tableCursor into @table

CREATE TABLE #TempTable( Tablename nvarchar(max), Bytes int, RowCnt int)

WHILE @@FETCH_STATUS = 0  
begin
    set @sql = 'insert into #TempTable (Tablename, Bytes, RowCnt) '
    set @sql = @sql + 'select '''+@table+''' "Table", sum(t.rowsize) "Bytes", count(*) "RowCnt" from (select (0'

    select @sql = @sql + ' + isnull(datalength([' + name + ']), 1) ' 
        from sys.columns where object_id = object_id(@table)
    set @sql = @sql + ') as rowsize from ' + @table + ' ) t '
    exec (@sql)
    FETCH NEXT FROM tableCursor INTO @table  
end

PRINT @sql

CLOSE tableCursor   
DEALLOCATE tableCursor

select * from #TempTable
select sum(bytes) "Sum" from #TempTable

DROP TABLE #TempTable
2

Here is a version of Romulus answer but that should work with schemas:

declare @table nvarchar(128)
declare @schema nvarchar(128)
declare @sql nvarchar(max)
SET @sql = ''
DECLARE tableCursor CURSOR FOR  
SELECT TABLE_NAME, TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @table, @schema

CREATE TABLE #TempTable( Tablename nvarchar(max), Schemaname nvarchar(max), Bytes bigint, RowCnt bigint)

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @sql = 'INSERT INTO #TempTable (Tablename, Schemaname, Bytes, RowCnt) '
    SET @sql = @sql + 'SELECT '''+@table+''' "Table",'''+@schema+''' "Schema", sum(t.rowsize) "Bytes", count(*) "RowCnt" FROM (SELECT (0'

    SELECT @sql = @sql + ' + ISNULL(DATALENGTH([' + name + ']), 1) '
        FROM sys.columns WHERE object_id = object_id(@schema + '.' + @table)
    SET @sql = @sql + ') AS RowSize FROM ' + @schema + '.' + @table + ' ) t'
    EXEC (@sql)
    FETCH NEXT FROM tableCursor INTO @table, @schema
END

CLOSE tableCursor   
DEALLOCATE tableCursor

SELECT Tablename "Table", Schemaname "Schema", Bytes, RowCnt "Rows", Bytes / RowCnt "Average"
    FROM #TempTable WHERE RowCnt > 0 ORDER BY Average DESC

DROP TABLE #TempTable
0

try this:

;WITH CTE as(select *,LEN(ISNULL(col1,''))+LEN(ISNULL(col2,'')) as row_len from yourtable)
select * from CTE where row_len > 8060
-1

I know this is an old question, but there's a better way:

select
    SomeOtherColumn,
    Bytes = datalength((select x.* from (values(null))data(bar) for xml auto))
from Table x
1
  • 3
    No, this is not a better way. It adds XML to the size. Even with Davids fix it assumes that all numbers are stored in text format (they are not). It also doesn't account for things like fixed size char or nchar. For my table it is off by a magnitude of 10. You might as well select a random number based on personal guess, it will probably be closer. Mar 25, 2021 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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