Re: mass alter table fields - script help
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 27 Jun 2007 14:40:49 +0000 (UTC)
rcamarda (robert.a.camarda@xxxxxxxxx) 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, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: mass alter table fields - script help
- From: rcamarda
- Re: mass alter table fields - script help
- References:
- mass alter table fields - script help
- From: rcamarda
- mass alter table fields - script help
- Prev by Date: Re: Parse field into multiple rows
- Next by Date: Re: Parse field into multiple rows
- Previous by thread: mass alter table fields - script help
- Next by thread: Re: mass alter table fields - script help
- Index(es):
Relevant Pages
|
