Hey,

I've just had to use this again and have made some changes, I realise I only really need to the length of the column for text columns so here it is:

select c.name as name,

' = ',

'@' + c.name as _name_param,

c.name + '_DESC' as name_ordered,

t.name + '(' + cast(c.prec as varchar(50)) + ')' as [col_def],

coldefinition =

case t.name

when 'nvarchar' then 'nvarchar' + '(' + cast(c.prec as varchar(50)) + ')'

when 'varchar' then 'nvarchar' + '(' + cast(c.prec as varchar(50)) + ')'

when 'char' then 'char' + '(' + cast(c.prec as varchar(50)) + ')'

when 'nchar' then 'nchar' + '(' + cast(c.prec as varchar(50)) + ')'

else t.name

end

+ ' = NULL'

from sysobjects s

inner join sys.syscolumns c on s.id = c.id

inner join sys.types t on t.user_type_id = c.xtype

where s.xtype='U'

and s.name = 'PolicyZoneMembership'