Solved

Alter column , entire table

Posted on 2009-05-05
8
234 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Mehram
  • 4
  • 4
8 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
Comment Utility
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
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
is it working properly?
0
 

Author Comment

by:Mehram
Comment Utility
is it working properly?

100 Percent.

Thanks and regards.
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
welcome
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Mehram
Comment Utility
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
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
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
 

Author Comment

by:Mehram
Comment Utility
ok.
0
 

Author Closing Comment

by:Mehram
Comment Utility
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now