mass alter table fields - script help

#1

Hello,
I need to alter fields in all my tables of a given database, and I
would to do this via a t-sql script.
Example, I want to change all fields called SESSION_ID to char(6). The
field is usually varchar(10), but the data is always 6 characters in
length. I have serveral fields that are fixed length that I want to
move from varchar to char.

I believe I can find all the tables where the field exists using
select * from INFORMATION_SCHEMA.columns where column_name =
'SESSION_Id'
but I dont know how to take this into an ALTER TABLE , ALTER COLUMN
that can be automated
TIA
Rob

#2

rcamarda (robert.a.camarda (AT) gmail (DOT) com) writes:

Quote:

I need to alter fields in all my tables of a given database, and I
would to do this via a t-sql script.
Example, I want to change all fields called SESSION_ID to char(6). The
field is usually varchar(10), but the data is always 6 characters in
length. I have serveral fields that are fixed length that I want to
move from varchar to char.

I believe I can find all the tables where the field exists using
select * from INFORMATION_SCHEMA.columns where column_name =
'SESSION_Id'
but I dont know how to take this into an ALTER TABLE , ALTER COLUMN
that can be automated

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name + ' char(6) NULL'
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
WHERE c.name = 'SESSION_ID'

Run, copy and paste result. If you want it entirely packed, run a cursor
over the query and run with EXEC().

Beware that this is likely to cause SQL Server to rebuild the table, so it
could take some time if tables are large.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

On Jun 27, 10:40 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

rcamarda (robert.a.cama... (AT) gmail (DOT) com) writes:
I need to alter fields in all my tables of a given database, and I
would to do this via a t-sql script.
Example, I want to change all fields called SESSION_ID to char(6). The
field is usually varchar(10), but the data is always 6 characters in
length. I have serveral fields that are fixed length that I want to
move from varchar to char.

I believe I can find all the tables where the field exists using
select * from INFORMATION_SCHEMA.columns where column_name =
'SESSION_Id'
but I dont know how to take this into an ALTER TABLE , ALTER COLUMN
that can be automated

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name + ' char(6) NULL'
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
WHERE c.name = 'SESSION_ID'

Run, copy and paste result. If you want it entirely packed, run a cursor
over the query and run with EXEC().

Beware that this is likely to cause SQL Server to rebuild the table, so it
could take some time if tables are large.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you Erland!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

mass alter table fields - script help

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

mass alter table fields - script help - 06-27-2007 , 07:30 AM

Re: mass alter table fields - script help - 06-27-2007 , 09:40 AM

Re: mass alter table fields - script help - 06-27-2007 , 10:25 AM