Using cursor variable as a tablename

[AnnetteBean]

I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks

[MAK]

Why you need to use cursor? Try this

select object_name(a.id), (select max(datalength(name)) from syscolumns where id =a.id)
from sysobjects a where type='u' order by name

[MAK]

The previous statement gives the actual data length which is 2 bytes for one character since it is nvarchar.

use this statement

select object_name(a.id), (select max(len(name)) from syscolumns where id =a.id)
from sysobjects a where type='u' order by name

[AnnetteBean]

That's great, will try that now.

Does this mean that you can't actually use the tablename from a variable?

[MAK]

declare @query varchar(2000
set @query = 'select '+@colname+', max(len('+@colname+')) from '+@table
exec (@query)

or use sp_executesql