question on using MAX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

question on using MAX

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-28 : 07:05:30

Is there any snazzy routines that someone has written to find the 'MAX(LEN(fieldname)) AS fieldname' in a SELECT statement for all fields in a table? As I have tables with several fields declared as nvarchar(255) which contain no mor than 20 chars in length and I would like to set the length accordingly. and writing a long sql statement with each fieldname in would be quite a job to do.

Something along the lines of :-

for each field in tablename
select MAX(LEN(fieldname)) AS fieldname

(which I can't quite get to work!)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 07:44:54

[code]DECLARE @SQL NVARCHAR(4000),
@tableName SYSNAME,
@schemaName SYSNAME

SELECT @schemaName = 'dbo',
@tableName = 'SomeTable'

SELECT @SQL = COALESCE(@SQL, '') + ' UNION ALL SELECT ' + QUOTENAME(COLUMN_NAME, '''')
+ ' AS columnName, MAX(LEN(' + QUOTENAME(COLUMN_NAME) + ')) AS maxChars FROM '
+ QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schemaName
AND TABLE_NAME = @tableName
AND DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR')

SET @SQL = STUFF(@SQL, 1, 11, '') + ' ORDER BY 1'

EXEC (@SQL)[/code]

E 12°55'05.63"
N 56°04'39.26"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-28 : 10:15:25

Thanks (again!) thats great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources