How to alter a column from char(2) to varchar(5) on 1000 Tables in 100 Databases on 5 servers?

I need to come up with a script to alter a column from char(2) to varchar(5) on 1000 Tables in 100 Databases on 5 servers. The column will most probably have 'Office' in the name. However: The column may have different name, not necessarily containing 'Office'. Sometimes a column is indexed, sometimes not. Sometimes a column is a Primary Key, sometimes not. Sometimes there are computed columns, sometimes not. Often there are many Views dependent on the abov开发者_如何学编程e tables. What would be the best methodology? I started with Red Gate's Compare and Dependency Tracker, etc. but there are many independent tables where a column needs to be altered.

If you provide some more info I may be able to give a more specific answer. Maybe you could give a couple of examples of the different scenarios.

The generic answer is you need to find the different scenarios and work out how you'd do it manually. e.g. If it's just changing a column with no dependencies then you the ALTER TABLE will give you the right result. If you're not comfortable with building the SQL you can use the table designer in Management Studio and rather than saving the changes there is an option to generate the SQL.

Once you've got the sample SQL for each scenario then use the INFORMATION_SCHEMA tables to build up some SQL dynamically to find each scenario and build the correct ALTER TABLE as one of the columns. e.g.

select 'ALTER TABLE [' + table_schema + '].[' + table_name + '] ALTER COLUMN [' + column_name + '] varchar(5);' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%office%'

Because you're going across multiple databases and servers you may be able to use the sys.databases or sp_msforeachdb to apply to each database. To go across multiple servers you may be able to use linked servers or because there's only 5 you could just apply the same process 5 times.

hope that helps

SQL Prompt 5 will have a feature to identify column dependencies. It's currently in early access so if you sign up you can try out the feature and provide us with feedback.

For more details: http://www.red-gate.com/messageboard/viewtopic.php?t=11846

To sign up: http://www.surveymk.com/s/DZLN2JW

In the meantime you can always use the free SQL Search tool to find references to an object in your SQL Server schema: http://www.red-gate.com/products/SQL_Search/

Hope this helps!