CODEWITHSUNDEEP
sqlsql-server
Sparsity
Sparsity

Reputation: 207

Counting the total number of characters in a SQL table

I want to count the total number of characters in a table.

I know how to do it for one column:

select sum(leng)
from (
    select len(column) as leng
    from table
) as Total

But how do I do this for every column in a table?

One way I have thought of is 

select sum(len(col1) + len(col2) + len(3) + ...) as TotalChracters
from Table

But I need to do this for over 500 tables, and each table has a lot of columns. I was hoping for a general code that works for one table which I can easily loop over in Python by just changing the table name on each loop.

Upvotes: 1

Views: 1803

Answers (1)

Angel M.
Angel M.

Reputation: 1358

I have some code that does not run the query but it is almost the same as it will the desired code for any number of tables and columns in your DB. Also, includes the ISNULL, so, if the table is empty it will show 0:

WITH CTE AS (
SELECT T.Name as Table_Name, 
    C.Name as Column_Name,
    ROW_NUMBER() OVER (PARTITION BY T.Name ORDER BY T.Name, C.Name) AS RowASC,
    ROW_NUMBER() OVER (PARTITION BY T.Name ORDER BY T.Name, C.Name DESC) AS RowDESC
FROM sysobjects T 
INNER JOIN syscolumns C
  ON T.ID = C.ID
WHERE T.XType = 'U'
)
SELECT *, 
    CASE WHEN RowASC = 1 THEN 'SELECT ISNULL(SUM(' ELSE '' END +
    CASE WHEN RowASC = 1 THEN 'LEN('+Column_Name+')' ELSE '+LEN('+Column_Name+')' END +
    CASE WHEN RowDESC = 1 THEN '), 0) AS [TotalCharacters on Table: '+Table_Name+']
    FROM '+Table_Name +' UNION ' ELSE '' END AS Query
FROM CTE
ORDER BY Table_Name, Column_Name;

Just need to copy the column at the Right (Query) and paste in a new query window.

Upvotes: 2

Related Questions