|
|
Posted by Erland Sommarskog on 06/27/07 14:40
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|