Solved

Alter many tables at once with the same column name

Posted on 2006-11-12
5
402 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 143

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 143

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

791 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