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.
| 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 |
|
|
|
|
|
|
Max field length from syscolumns