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

Published 29 July 07 01:39 AM | SQL Master 


    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

This is useful when you need to change the collation for the columns in that table.

Comments

# SSQA.net - SqlServer-QA.net said on July 29, 2007 12:30 PM:

SELECT 'ALTER TABLE ' + sysobjects.name + ' ' + 'ALTER COLUMN ' + syscolumns

# Other SQL Server Blogs around the Web said on August 6, 2007 9:53 PM:

SELECT 'ALTER TABLE ' + sysobjects.name + ' ' + 'ALTER COLUMN ' + syscolumns

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.