Max field length from syscolumns

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

SQL Server 2000 Forums

Transact-SQL (2000)

Max field length from syscolumns

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2009-09-22 : 04:30:27

Hello experts,

I need to write a query that lists out all syscolumns with the column name, column order and column length. No problem - I have that:

SELECT syscolumns.name AS [Column],
syscolumns.length AS ColumnLength
FROM sysobjects
INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = 'mytable')

What I need is an extra column in the output that shows the max length for each field so I can compare and identify problems. All columns are varchar.

Thanks for your help folks. It is much appreciated.

Billsack

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 04:54:54

Maximum length from table data?

Madhivanan

Failing to plan is Planning to fail

billsack
Starting Member

35 Posts

Posted - 2009-09-22 : 06:24:37

Thanks. I know that but I am trying to optimise the query without specifying 400 field names.

I have got somewhere with borrowed code from the web but am hitting a snag with varchar (8000) simply because the table is structured so badly. Unfortunately there is nothing I can do about the structure.

I have this query working:

DECLARE @SQL varchar (8000)

SET @SQL = ''

SELECT @SQL = @SQL + ' SELECT MAX(LEN(' + QUOTENAME(sc.name) + ')) FROM mytable union all'
FROM syscolumns sc
WHERE sc.ID = OBJECT_ID('mytable')

SET @SQL = LEFT(@SQL, LEN(@SQL - 9))
PRINT @SQL
EXEC(@SQL)

Any suggestions?!!! I have looked at this for the best part of a day now.
Billy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources