How to determine the maximum length / value for each column in your database?

You might want to use this query (found it here) and made a few modifications. It’s an older query, but this may point you in the right direction.

DECLARE @Results table
(
ID uniqueidentifier,
SchemaName varchar(250),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
NumericPrecision varchar(250),
DatetimePrecision varchar(250),
IsNullable varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @Results
SELECT
NEWID(),
t.TABLE_SCHEMA,
t.TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CASE
WHEN DATA_TYPE NOT IN ('nvarchar','varchar') THEN 'N/A'
WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'Max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar)
END,
NUMERIC_PRECISION,
DATETIME_PRECISION,
IS_NULLABLE,
'NA',
'SELECT Max(Len(' + COLUMN_NAME + ')) FROM ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'

DECLARE @id varchar(36)
DECLARE @sql varchar(200)
DECLARE @receiver table(theCount int)

DECLARE length_cursor CURSOR
FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (theCount)
exec(@sql)

UPDATE @results
SET Longest = (SELECT theCount FROM @receiver)
WHERE ID = @id

DELETE FROM @receiver

FETCH NEXT FROM length_cursor
INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT
SchemaName,
TableName,
ColumnName,
DataType,
MaxLength,
NumericPrecision,
DatetimePrecision,
IsNullable,
Longest
FROM
@Results





Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s