Update field length on all DB's

[robertfah]

Is there a way to programmatically change the length of a varchar field on a specific table across multiple DB's? I've got a field in a table that I need to make the length bigger (currently it's varchar(50) and needs to be varchar(200)) and I've got to do it across 35+ DB's. All the tables and fields are the same in each DB so if I get it for one, I should be able to get it for all of them.

 

[gmmastros]

[!]PLEASE make sure you have a backup of all your db's before doing this.[/!]

Run this in a query window. It should return the TSQL you need to update the column in all your tables.

Select 'Alter Table [' + Name + '].[dbo].[YourTableName] Alter Column [YourColumnName] VarChar(200)' From sys.databases
Where  database_id > 4

After running the query, you should be able to copy/paste the output to a new query window.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Thanks GMM...I'll give this a try once I get a chance to back it up. I will let you know if it works.