CODEWITHSUNDEEP
sqlsql-server
Junaid
Junaid

Reputation: 1030

Print max number of characters of all varchar columns in all the tables of the database in one query

I am trying to print the max number of characters in varchar/nvarchar etc. type of columns from all tables in the database.

What I have done so far:

DECLARE @TEMP_TABLE TABLE(TABLE_NAME VARCHAR(MAX))
DECLARE @TABLE_NAME VARCHAR(MAX)

INSERT @TEMP_TABLE
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS

WHILE(1 = 1)
BEGIN
    SET @TABLE_NAME = NULL
    SELECT TOP(1) @TABLE_NAME = TABLE_NAME
    FROM @TEMP_TABLE
    
    IF @TABLE_NAME IS NULL
        BREAK

    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME =  @TABLE_NAME AND DATA_TYPE IN ('varchar', 'text', 'nvarchar', 'ntext', 'image', 'binary', 'nbinary')

    DELETE TOP(1) FROM @TEMP_TABLE    
END

I have simply applied a loop which will end once the table records have ended in the temp table. I am able to print the table name, column name, and datatype but not the max length in each column. I tried using Max(Len(COLUMN_NAME)) but I am unable to do it with the Group By clause.

Upvotes: 1

Views: 45

Answers (1)

Simeon
Simeon

Reputation: 836

The loop is unnecessary, in SQL Server you can get all the data from a single query to the sys tables

select 
    o.name [table_name],
    c.name [column_name],
    t.name [data_type],
    t.max_length
from sys.columns c
inner join sys.tables o on c.object_id = o.object_id
inner join sys.types t on c.system_type_id = t.system_type_id

unless of course you want seperate query outputs in which case you can add where o.name = @TABLE_NAME to the end and put this in the loop

EDIT

Here is an adaptation of your query that returns a table with the maximum length of the value in all columns in the database. It works by executing dynamic sql for each column.

DECLARE @table VARCHAR(MAX)
DECLARE @column VARCHAR(MAX)
DECLARE @type VARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)
DECLARE @max int
DECLARE @count int

IF OBJECT_ID('tempdb..#table_columns','U') is not null drop table #table_columns
IF OBJECT_ID('tempdb..#temp_table','U') is not null drop table #temp_table

select
    o.name [table_name],
    c.name [column_name],
    t.name [data_type],
    N'select @outputFromExec = max(len([' + c.name + '])) from [' + DB_NAME() +'].['+ SCHEMA_NAME(o.schema_id) +'].[' + o.name + ']' query
into #TEMP_TABLE
from sys.columns c
inner join sys.tables o on c.object_id = o.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
where t.name IN ('varchar', 'text', 'nvarchar', 'ntext', 'image', 'binary', 'nbinary')

WHILE(1 = 1)
BEGIN
    SET @query = NULL
    SELECT TOP(1) @table = [table_name], @column = [column_name], @type = [data_type], @query = query
    FROM #TEMP_TABLE
    
    IF @query IS NULL
        BREAK

    EXECUTE sp_executesql @query, N'@outputFromExec int out', @max out
    

    IF OBJECT_ID('tempdb..#table_columns','U') is null
    BEGIN
        SELECT @table [table_name], @column [column_name], @type [data_type], @max [max_length] into #table_columns
    END

    insert into #table_columns values(@table, @column, @type, @max)

    DELETE TOP(1) FROM #TEMP_TABLE    
END

select * from #table_columns where max_length

Upvotes: 1

Related Questions