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

Is SQL Server's 2005's Exception Handling an Improvement?

At first, I was excited to read about the new TRY...CATCH exception handling in SQL Server 2005, but it feels like it has a serious flaw: only the last error message is returned in the Exception object!

This is really annoying, both during development and in production. For example, if you make a mistake while trying to create a CONSTRAINT:

-- Setup.
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblInvoice')
   DROP TABLE tblInvoice

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblCustomer')
   DROP TABLE tblCustomer

CREATE TABLE tblCustomer
(
   CustomerID INT,
)

CREATE TABLE tblInvoice
(
   InvoiceID INT,
   CustomerID INT,
)

Under SQL Server 2000, we get complete error information:

ALTER TABLE tblInvoice
   ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID)
   REFERENCES dbo.tblCustomer(CustomerIDDDDD)    -- Error!

Msg 1770, Level 16, State 0, Line 18
Foreign key 'FK_tblInvoice_tblCustomer' references invalid column 'CustomerIDDDDD' in referenced table 'dbo.tblCustomer'.

Msg 1750, Level 16, State 0, Line 18
Could not create constraint. See previous errors.

But under SQL Server 2005's exception mechanism, the error message for this code is almost useless:

BEGIN TRY
   ALTER TABLE tblInvoice
      ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID)
      REFERENCES tblCustomer (CustomerIDDDDD)
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE()
END CATCH

Msg 50000, Level 16, State 42, Line 24
Could not create constraint. See previous errors.

Microsoft's NaveenP explains ( http://blogs.msdn.com/sqlprogrammability/archive/2006/04/03/567550.aspx ):

"When an error is raised in a some special context, the first message has information about the error, while subsequent messages provides information about the context. This becomes an issue inside tsql try-catch. In the absence of any notion about error collection, the catch block is activated with one of the error messages. As Sql Server 2000 would have set @@error to the last error message, we decided to set error intrinsics (error_message(), etc.) to the last error message inside the catch block. In other words Sql Server 2005 would ignore all but the last error message inside [the] tsql try-catch... Backward compatibility prevents us from mergeing old error messages."

Well, shoot.

Find and Fix NOT NULL Columns Without DEFAULT Constraints

The latest and greatest version of my FindAllNotNullColumnsWithNoDefaultConstraint.sql script (formerly, embarrassingly, FindAllNonNullableColumnsWithNoDefaultConstraint.sql) is now available. It returns the DML code to add DEFAULT constraints to all NOT NULL columns that don't already have them. Useful on legacy databases to head off problems before they happen.

This is a work in progress, so the usual caveats apply: do not run this on any database you want to keep, for example, a production database, or any database that isn't backed up. Comments welcome!

Find NOT NULL Columns Without DEFAULT Constraints

This is really handy when you have a large legacy database where the previous owner forgot to add DEFAULT constraints to the NOT NULL columns.

This script has been moved to the Schema Analysis scripts page.