Alter column , entire table

Please rectify where clause of the following code to eliminate identity column.
select not_null = 'alter table ' + table_name + ' alter column ' 
                  + column_name + ' ' + data_type
                  
                  + case when data_type = 'numeric' then '(' else '' end 
                  + case when data_type = 'numeric' then convert(varchar,numeric_precision_radix) else '' end
                  + case when data_type = 'numeric' then ',' else '' end
                  + case when data_type = 'numeric' then convert(varchar,numeric_scale) else '' end 
                  + case when data_type = 'numeric' then ')' else '' end
                  + ' not null '
,default_0 = 'alter table ' + table_name 
                  + ' add default 0 for ' + column_name
from information_schema.columns
where data_type not like '%char%'
and   data_type not like '%time%'
and   data_type not like '%image%'
and   data_type not like '%binary%'
and   column_default is null
order by data_type

Open in new window

MehramAsked:
Who is Participating?
 
RiteshShahCommented:
below script remove listing column with identity



select not_null = 'alter table ' + table_name + ' alter column ' 
                  + column_name + ' ' + data_type
                  
                  + case when data_type = 'numeric' then '(' else '' end 
                  + case when data_type = 'numeric' then convert(varchar,numeric_precision_radix) else '' end
                  + case when data_type = 'numeric' then ',' else '' end
                  + case when data_type = 'numeric' then convert(varchar,numeric_scale) else '' end 
                  + case when data_type = 'numeric' then ')' else '' end
                  + ' not null '
,default_0 = 'alter table ' + table_name 
                  + ' add default 0 for ' + column_name
from information_schema.columns
where data_type not like '%char%'
and   data_type not like '%time%'
and   data_type not like '%image%'
and   data_type not like '%binary%'
and   column_default is null
and columnproperty(object_id(table_name), column_name,'IsIdentity') = 0
order by data_type

Open in new window

0
 
RiteshShahCommented:
is it working properly?
0
 
MehramAuthor Commented:
is it working properly?

100 Percent.

Thanks and regards.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RiteshShahCommented:
welcome
0
 
MehramAuthor Commented:
BTW,

How to avoid these tables from the list,

MSpub_identity_range
syspublications
syncobj_0x3931454436303542
syncobj_0x3931454436303542
syncobj_0x3031323230353944
syncobj_0x3031323230353944
syncobj_0x3031323230353944
syncobj_0x3031323230353944
syncobj_0x3031323230353944
MSpeer_lsns
MSpeer_lsns

I tried to take help from objectproperty but could not suceed.
0
 
RiteshShahCommented:
can't you use something like this ?



select not_null = 'alter table ' + table_name + ' alter column ' 
                  + column_name + ' ' + data_type
                  
                  + case when data_type = 'numeric' then '(' else '' end 
                  + case when data_type = 'numeric' then convert(varchar,numeric_precision_radix) else '' end
                  + case when data_type = 'numeric' then ',' else '' end
                  + case when data_type = 'numeric' then convert(varchar,numeric_scale) else '' end 
                  + case when data_type = 'numeric' then ')' else '' end
                  + ' not null '
,default_0 = 'alter table ' + table_name 
                  + ' add default 0 for ' + column_name
from information_schema.columns
where data_type not like '%char%'
and   data_type not like '%time%'
and   data_type not like '%image%'
and   data_type not like '%binary%'
and   column_default is null
and columnproperty(object_id(table_name), column_name,'IsIdentity') = 0
and table_name not like '%#%'
and table_name not like '%syncobj%'
and table_name not like '%MSpub%'
and table_name not like '%MSpeer%'
order by data_type

Open in new window

0
 
MehramAuthor Commented:
ok.
0
 
MehramAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.