I ran into an issue where I needed to change a list of columns from text and ntext in SQL Server 2000 to varchar(max) and nvarchar(max) fields in SQL Server 2005. Not wanting to do this one field at a time, I poked around and found a solution using the built in Information_Schema.
To simply produce a list of table names, column names or data types you can do something like this:
SELECT table_name,column_name, data_type
FROM INFORMATION_SCHEMA.Columns
WHERE column_name like '%FirstName%'
ORDER BY table_name
To produce a list of ALTER TABLE statements that will change a column type from Text to Varchar(MAX), run this in Query Analyzer or Management Studio.
SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' VARCHAR(MAX);'
FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'TEXT'
ORDER BY TABLE_NAME
This will produce a list of ALTER TABLE statements that look like this:
ALTER TABLE AuditorFees ALTER COLUMN AuditorChangeNotes VARCHAR(MAX);
ALTER TABLE CEOCompFiguresNotes ALTER COLUMN NotesCEOContract VARCHAR(MAX);
/* etc.
Copy and paste these invidual ALTER TABLE statements in Query Analyzer or Management Studio and run them. Problem solved!