Sean Lange (4/3/2013)

---

erikd (4/3/2013)

---

Sean Lange (4/3/2013)

---

Using your dynamic code as an example this will produce the same results but as a single query instead of 1 query for each column.

declare @thing nvarchar(max), @table sysname = 'SomeTable'

select top 1 @thing = 'SELECT ''' + @table + ''' as TableName, ' + STUFF(

(

select ', MAX(LEN(' + COLUMN_NAME + ')) as ' + COLUMN_NAME

from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'

for XML path('')), 1, 1, '') + ' FROM [' + @table + ']'

from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'

exec sp_executesql @thing

Did this code run for you? Even specifying a table in the declare, I got errors:

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 3

Must declare the scalar variable "@table".

Msg 137, Level 15, State 2, Line 6

Must declare the scalar variable "@table".

Since you are using 2005 you have to separate the declaration and the assignment.

declare @thing nvarchar(max), @table sysname

set @table = 'SomeTable'

Then it should work for you.