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".
I was able to run the code unmodified without errors. May I ask what version of SQL Server you are running and are you running the code just as posted or did you include it in a procedure of some sort.