Solved

Alter many tables at once with the same column name

Posted on 2006-11-12
5
400 Views
Last Modified: 2012-06-21
I am running SQL server 2005

Is there a way to change all columns with the same name from char(2) to char(3)?

There are over 150 tables in the schema that have to be altered.

Thanks in advance.
0
Comment
Question by:parikaa
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17928380
run this

SELECT 'ALTER TABLE '+TABLE_NAME +' ALTER COLUMN '+COLUMN_NAME +' CHAR(3)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'putTableName'
AND COLUMN_NAME  = 'puturColumnName'

Take the output and paste it on a new query page and run that
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17928390
you could script out all the changes to be done by querying the system view sys.columns on limiting to those having data type char and length 3.

select ' ALTER TABLE [' + object_name(object_id) + '] ALTER [' + column +'] CHAR(3) '
from sys.columns
where max_length = 2
and system_type_id = X

where X is a value I have to figure out, or you can do yourself...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17928434
select ' ALTER TABLE [' + object_name(object_id) + '] ALTER COLUMN [' + column +'] CHAR(3) '
from sys.columns
where max_length = 2
and system_type_id = 175
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17928441
SELECT 'ALTER TABLE '+TABLE_NAME +' ALTER COLUMN '+COLUMN_NAME +' CHAR(3)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
--TABLE_NAME = 'putTableName'
--AND COLUMN_NAME  = 'puturColumnName' AND
 DATA_TYPE  = 'char'
AND CHARACTER_MAXIMUM_LENGTH = 2

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17928666
declare @table varchar(128), @stmt varchar(1000)

Declare c cursor for select table_name from information_schema.columns where column_name ='yourcolumn'
open c
fetch next from c into @table
while @@fetch_status = 0
begin
set @stmt = 'alter table ['+@table+'] alter column columnname char(3) null'
exec (@stmt)
fetch next from c into @table
end
close c
deallocate c
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

16 Experts available now in Live!

Get 1:1 Help Now