CODEWITHSUNDEEP
sqlsql-serverindexing
Janek Podwysocki
Janek Podwysocki

Reputation: 593

How to calculate data length in each column (in a single table) in SQL Server?

This code provide a possibility to sum a datalength in a single column:

    select sum(datalength(column_1))
    from my_table

This part provide me an information about columns in my_table

SELECT COLUMN_NAME,TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table' AND TABLE_SCHEMA='my_schema'

and now i would like to add a column to second select result with sum of data length for each column, so i nedd a column like this:

sum(datalength(col_1))
sum(datalength(col_2))
    .  
    .          
    .
sum(datalength(col_n))

Could you help me to prepare a proper select to achieve explained result?

EDIT: I need to count how many megabytes have elements in each column (in all rows):

1) if its int type column it is easy: (size of int * number of rows)

2) when column is a varchar i would like to add size of each varchar in this column

Upvotes: 0

Views: 1904

Answers (2)

Thom A
Thom A

Reputation: 95574

Is this what you're after?

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Schema sysname = N'dbo', --Replace with appropriate Schema
        @Table sysname = N'PerformanceTest'; --Replace with appropriate Table

SET @SQL = N'SELECT ' + 
           STUFF((SELECT N' +' + @CRLF +
                         N'       ISNULL(SUM(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + N')),0)'
                  FROM INFORMATION_SCHEMA.COLUMNS C
                  WHERE C.TABLE_SCHEMA = @Schema
                    AND C.TABLE_NAME = @Table
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,11,N'') +N' AS DataLengthTotal' + @CRLF +
          N'FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N';';

PRINT @SQL;

EXEC sp_executesql @SQL;

Seems the OP is not after a grant total, but total by column. This is can be easily be changed by changing the below

SELECT N' +' + @CRLF +
       N'       ISNULL(SUM(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + N')),0)'

To the below:

SELECT N', ' + @CRLF +
       N'       ISNULL(SUM(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + N')),0) AS ' + QUOTENAME(CONCAT(C.COLUMN_NAME,N'DataLength'))

And removing the alias after the STUFF expression.

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

If I understand you correctly and you want to " ... add a column to second select result (SELECT ... FROM INFORMATION_SCHEMA.COLUMNS ...) with sum of data length for each column ... ", a dynamic statement should be an option:

DECLARE @stm nvarchar(max) = N''
SELECT @stm = CONCAT(
    @stm,
    N'UNION ALL ',
    N'SELECT ''',
    COLUMN_NAME, 
    N''' AS [COLUMN_NAME], ''',
    TABLE_SCHEMA, 
    N''' AS [TABLE_SCHEMA], ''',
    TABLE_NAME, 
    N''' AS [TABLE_NAME], ',
    CONVERT(nvarchar(max), ORDINAL_POSITION), 
    N' AS [ORDINAL_POSITION], ''',
    DATA_TYPE, 
    N''' AS [DATA_TYPE], ',
    N'(SELECT SUM(DATALENGTH(',
    COLUMN_NAME,
    N')) FROM ',
    QUOTENAME(TABLE_SCHEMA),
    N'.',
    QUOTENAME(TABLE_NAME),
    N') AS [DATA_LENGTH] ' 
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable' AND TABLE_SCHEMA = 'YourSchema'
SELECT @stm = STUFF(@stm, 1, 10, N'')

PRINT @stm
EXEC sp_executesql @stm

Upvotes: 2

Related Questions