Alter Data types for Multiple TABLES

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alter Data types for Multiple TABLES

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-05-15 : 23:36:38

Guys,

I have around 200 tables which have differant fields using integer data types..
Now I want to loop through all tables and Indivisual fields and check its data type if it is INT, then change it to Bigint (USING Alter Table)

Is there any way I could do tha programatically?

Thanks in advance.

SKR

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-15 : 23:50:35

you can use sysobjects join with syscolumns and get all the columns in all tables with datatype = int, and generate an ALTER TABLE and compile it.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 00:10:35

don't forget to filter for xtype = 'U'.

You may also use INFORMATION_SCHEMA.COLUMNS to do the job. But you will have to filter out the VIEWs from INFORMATION_SCHEMA

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-05-16 : 00:14:12

This is brilliant..
elect sysobjects.name,syscolumns.name,systypes.name from sysobjects inner join syscolumns on
sysobjects.id = syscolumns.id
inner join systypes on
syscolumns.xtype = systypes.xtype
where sysobjects.xtype = 'U' and systypes.name = 'Int'

SKR

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-05-16 : 00:15:26

Thanks guys very much... Sorry forgot to say in last post....cheers.

SKR

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 00:15:38

This will give you an idea

Select 'Alter table '+table_name+' alter column '+column_name+' bigint '
from information_schema.columns where data_type='INT'

Copy the result back to QA and run

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 00:16:54

quote:
Originally posted by madhivanan

This will give you an idea

Select 'Alter table '+table_name+' alter column '+column_name+' bigint '
from information_schema.columns where data_type='INT'

Copy the result back to QA and run

Madhivanan

Failing to plan is Planning to fail

Don't forget to exclude VIEWs. View is included in information_schema.columns

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 00:32:14

Good Point Tan

Madhivanan

Failing to plan is Planning to fail

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-05-16 : 00:45:21

Thanks guys. a problem came up..
While I try to Alter exisitng Int fields which are basically (Identity colums)
I keeps on getting this message
The object 'PK__table_SpecificDi__0551FE35' is dependent on column 'id'.

This is a clustered index, which I beleive automatically get created when you make it as Identity column..
I don't want to mess around with Identity values of Int.. all I want to do is to upgrade to BIGINT....with exisitng identity.

I tried through Enterprize M, and it works from there..

Any quick ideas please...

Thanks in advance

SKR

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-15 : 11:41:48

quote:
Originally posted by khtan

don't forget to filter for xtype = 'U'.

You may also use INFORMATION_SCHEMA.COLUMNS to do the job. But you will have to filter out the VIEWs from INFORMATION_SCHEMA

KH

If I just use INFORMATION_SCHEMA.COLUMNS view, please tell me how
1/ I can know which objects are views and which are tables?
2/ I can filter system tables?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources