SQL Server – Select Column Data from sys.column Info

sql serversystem-tables

Either this is impossible, or I can't write a good internet search.

I have a query to get the table and column information for our database:

Select t.name [table], c.name [column]
From sys.tables t
Left join sys.columns c on t.object_id = c.object_id

What I'd like to do is add in something like

(Select AVG(LEN(c.name)) from t.name) [AVG LEN]

I'd obviously build in some checks to only run on varchar for Len, and regular AVG for numerics, but that part would be easy. It is getting SQL to actually treat the names correctly that I'm struggling with.

Best Answer

You can use dynamic SQL, a cursor and a global temporary table to collect this information. Note that performance won't be great (cursors are generally a bad idea) but if you only need to run this as a once-off then it should be fine.

CREATE TABLE ##GlobalTemp
(
  TableName VARCHAR(255),
  ColumnName VARCHAR(255),
  Min INT,
  Max INT,
  Avg INT
)

DECLARE @SqlCmd NVARCHAR(MAX),
    @TableName NVARCHAR(255),
    @ColumnName NVARCHAR(255),
    @Type BIT -- 0 = integer, 1 = character

DECLARE C1 CURSOR FOR
    SELECT QUOTENAME(s.[name]) + '.' + QUOTENAME(t.[name]), c.[name],
        CASE 
            WHEN ty.[name] IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN 1
            WHEN ty.[name] IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'bit', 'decimal', 'numeric', 'smallmoney', 'bigint') THEN 0
        END
    FROM sys.columns c
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id

OPEN C1

FETCH NEXT FROM C1 INTO @TableName, @ColumnName, @Type

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SqlCmd = 'INSERT INTO ##GlobalTemp (TableName, ColumnName, Min, Max, Avg)
    SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', ' + 
        CASE @Type
            WHEN 0 THEN 'MIN(' + @ColumnName + '), MAX(' + @ColumnName + '), AVG(' + @ColumnName + ')'
            WHEN 0 THEN 'MIN(LEN(' + @ColumnName + ')), MAX(LEN(' + @ColumnName + ')), AVG(LEN(' + @ColumnName + '))'
        END + '
    FROM ' + @TableName + ';'

    PRINT @SqlCmd
    EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @TableName, @ColumnName, @Type
END

CLOSE C1
DEALLOCATE C1

SELECT *
FROM ##GlobalTemp

DROP TABLE ##GlobalTemp
Related Question