0

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.

3
  • 2
    what is the output that you are getting and what is the output that you desire? Feb 19 '20 at 3:36
  • Are you trying to find out the average length of column names? Average row size? Why?
    – vonPryz
    Feb 19 '20 at 8:26
  • For varChars, I'm aiming to get the min, avg, max, and mode lengths of the string values. I only listed the AVG in the post to keep it simple. I'm also going to do some work with numeric values as well. This way as I go to develop controls for these fields, I can better estimate the size to make the controls. I could do this column by column, but I'd like to be able to just be able to run one query and get my tables and columns in one nice chart.
    – CervonMi
    Feb 19 '20 at 20:46
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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