Alter column , entire table

is it working properly?

is it working properly?

100 Percent.

Thanks and regards.

welcome

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.

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

Select allOpen in new window

ok.

Thanks