I want to generate a script that updates a column on every table in my database. How do I do this?

The simplest way to do this is to use SQL Query Analyser. The trick is to concatenate the SQL command you want to run on every table with with the sysobjects table. For example, to  update all columns in your database to a new collation you could run the following query, and use the output of this for your new script.

    SELECT 'ALTER TABLE ' + sysobjects.name + ' ' +
    'ALTER COLUMN ' + syscolumns.name + ' ' +
    systypes.name + '(' + cast(syscolumns.length as varchar) + ') ' +
    'COLLATE ' + syscolumns.collation + ' ' +
    case when syscolumns.isnullable = 1 then 'NULL' else 'NOT NULL' end sql
    from syscolumns
    inner join sysobjects
            on sysobjects.id = syscolumns.id
    inner join systypes
            on systypes.xtype = syscolumns.xtype
    where syscolumns.collation is not null
    and sysobjects.xtype = 'U'

Spot an error? Please comment about this article

Keywords: Hot Tip - SQL Server 2000    

New Search SSW Custom Support