Use Central Management Servers to Alter a Column in All Servers

The "Central Management Servers" functionality of SQL Server Management Studio is an excellent way to apply the same code to every server. By "every server", I mean groups of servers that you define: you might have different servers in folders named "Production", "Development", "QA", "SQL Server 2000", etc. It just depends on your needs.

To get to the "Central Management Servers" pane in SSMS, simply select the "View, Registered Servers" menu item. After you've set up your servers, you can execute queries against every database in every server. The example below changes the width of a column in a table. Note that in this example, we look for only databases whose name begins with "DB100", we exclude read-only databases, and we specify the schema, table, and column name we want to modify. The code also checks that the length isn't already set to the value we want, to avoid unnecessary noise in the output. This code is compatible with SQL Server 2000 and later (hence the use of "syscolumns", etc.)

-- Comment. 
set nocount on
declare @Sql nvarchar(4000)

set @Sql = 'use [?]

            if exists (select *
                         from syscolumns   sc
                         join sysobjects   so
                           on sc.id = so.id
                        where ''?''                                   like ''DB100%''
                          and databaseproperty(''?'', ''IsReadOnly'')    = 0
                          and user_name(so.uid)                          = ''dbo''
                          and object_name(sc.id)                         = ''Customer''
                          and sc.name                                    = ''FirstName''
                          and sc.length                                 != 120)
            begin
                select ''?'', sc.length
                  from syscolumns   sc
                  join sysobjects   so
                    on sc.id = so.id
                 where ''?''                                   like ''DB100%''
                   and databaseproperty(''?'', ''IsReadOnly'')    = 0
                   and user_name(so.uid)                          = ''dbo''
                   and object_name(sc.id)                         = ''Customer''
                   and sc.name                                    = ''FirstName''
                   and sc.length                                 != 120

                ALTER TABLE dbo.Customer ALTER COLUMN FirstName VARCHAR(120) NULL
            end '

exec sp_msforeachdb @Sql
go

References

For more information on Central Management Servers:

     Create a Central Management Server and Server Group

     Registered Servers and Central Management Server Stores

     Execute SQL Server query on multiple servers at the same time

List All Database Objects By Type and Owner

This script lists all the objects in the current database.

-- ListOwnersForAllObjects.sql
-- SQL Server 2000 and later.
SELECT
   NAME               AS Object,
   USER_NAME(uid)     AS Owner,
   CASE (xtype)
      WHEN 'AF' THEN 'Aggregate function (CLR)'
      WHEN 'C'  THEN 'CHECK constraint' 
      WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
      WHEN 'F'  THEN 'FOREIGN KEY constraint'
      WHEN 'PK' THEN 'PRIMARY KEY constraint'
      WHEN 'P'  THEN 'SQL stored procedure'
      WHEN 'PC' THEN 'Assembly (CLR) stored procedure'
      WHEN 'FN' THEN 'SQL scalar function'
      WHEN 'FS' THEN 'Assembly (CLR) scalar function'
      WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
      WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
      WHEN 'RF' THEN 'Replication-filter-procedure'
      WHEN 'S'  THEN 'System base table'
      WHEN 'SN' THEN 'Synonym'
      WHEN 'SQ' THEN 'Service queue'
      WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
      WHEN 'TR' THEN 'SQL DML trigger '
      WHEN 'IF' THEN 'SQL inline table-valued function'
      WHEN 'TF' THEN 'SQL table-valued-function'
      WHEN 'U'  THEN 'Table (user-defined)'
      WHEN 'UQ' THEN 'UNIQUE constraint'
      WHEN 'V'  THEN 'View'
      WHEN 'X'  THEN 'Extended stored procedure'
      WHEN 'IT' THEN 'Internal table'
   END            AS 'Object Type'
  FROM sysobjects
 WHERE USER_NAME(uid) NOT IN ('sys', 'INFORMATION_SCHEMA')
 ORDER BY 'Object Type', Owner, Object