mass alter table fields - script help

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

Re: mass alter table fields - script help

rcamarda (robert.a.camarda@gmail.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: mass alter table fields - script help

On Jun 27, 10:40 am, Erland Sommarskog wrote:
> rcamarda (robert.a.cama...@gmail.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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thank you Erland!