|
|
Posted by rcamarda on 06/27/07 15:25
On Jun 27, 10:40 am, Erland Sommarskog <esq...@sommarskog.se> 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/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thank you Erland!
Navigation:
[Reply to this message]
|