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
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'
Hot Tip - SQL Server 2000
SSW Custom Support