Master SQL Query - Find all colums in 1 table which have bigger type than containing maximum value

Question

Alright for example i have varchar 200 column but it has maximum 25 varchar value. So this query should return this column. This query should run through all columns in selected table and return all this kind of results. So we can examine column structure and take appropriate action.

What i mean is : "find me all columns that are defined as wider than the largest actual data value in them"

Answer 1

You didn't indicate what RDBMS so I took as general of an approach as I could. The INFORMATION_SCHEMA tables are generally supported by large RDBMS installations (it's a standard, for all that it is worth). If not, tweak this to suit to meta data available as well as syntax oddities. The approach itself is sound.

SET NOCOUNT ON;

-- Create a local table for processing
DECLARE
    @RESIZING TABLE
(
    resizing_id int identity(1,1) NOT NULL PRIMARY KEY
,   schemaname sysname
,   tablename sysname
,   columnname sysname
,   datatype sysname
,   max_length int
,   current_length int
);

-- Use the ANSI standard view for system meta data
-- to populate tables. Only focusing on varchar fields
-- as they are the only ones that will provide useful
-- sizing information. 
-- To make it work with chars, update the query individual
-- queries to perform a trim operation before calling len
-- and obviously update the data_type to include char
INSERT INTO
    @RESIZING 
SELECT
    ISC.TABLE_SCHEMA
,   ISC.TABLE_NAME
,   ISC.COLUMN_NAME
,   ISC.DATA_TYPE
,   ISC.CHARACTER_MAXIMUM_LENGTH
,   NULL AS current_length
FROM
    INFORMATION_SCHEMA.COLUMNS ISC
WHERE
    ISC.DATA_TYPE = 'varchar';


-- Create a cursor
-- Kill a kitten
DECLARE
    Csr CURSOR 
FOR
SELECT
    -- build out a query like
    -- SELECT @current_len = MAX(LEN(X.[COLUMN_NAME])) FROM [dbo].[TABLE] X WITH(NOLOCK) 
    'SELECT @current_len = MAX(LEN(X.[' 
    + R.columnname 
    + '])) FROM [' 
    + R.schemaname 
    + '].[' 
    + R.tablename 
    + '] X WITH(NOLOCK) ' AS query
,   R.current_length
FROM
    @RESIZING R
FOR UPDATE OF current_length;

-- 2 local variables, one for the dynamic query
-- one to hold the results of said query
DECLARE
    @query nvarchar(max)
,   @current_length int;

OPEN
    Csr;

FETCH NEXT 
FROM Csr
INTO @query, @current_length;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- try before you buy
    PRINT @query;

    -- Run the query, assigning length to @current_length variable
    EXECUTE sp_executesql @query, N'@current_len int OUTPUT', @current_len = @current_length OUTPUT;

    -- Push results int our temporary table
    UPDATE
        R
    SET
        current_length = @current_length
    FROM
        @RESIZING R
    WHERE
        CURRENT OF Csr;

    FETCH NEXT 
    FROM Csr
    INTO @query, @current_length;
END

CLOSE Csr;
DEALLOCATE Csr;


-- Result the resultset for all the
-- tables with longer lengths than used
-- (NULLS included)
SELECT
    *
FROM
    @RESIZING R
WHERE
    R.max_length > isnull(@current_length, 0)

Results (SQL Server 2008 R2)

resizing_id | schemaname | tablename    | columnname | datatype | max_length | current_length
1           | dbo        | DupesOk      | name       | varchar  | 50         | 12
2           | dbo        | SALES_HEADER | CCCode     | varchar  | 15         | 15
3           | lick       | ABC          | value      | varchar  | 50         | 8