Sometimes you want to make changes across all tables in a particular database.

For example, lets say you’ve got a database that was replicated and now its not and you want to strip the msrepl_tran_version column. During the replication process a column is added to all tables to ensure that there is uniqueness across all the rows. When replication is stopped thereĀ  is no longer a need to have this column, in fact its confusing being thereĀ  because it doesn’t do anything. The T-SQL below will generate script which when run in a query window will alter all the table in a particular database – it will drop the msrepl_tran_version to be precise,

USE <<DATABASE>>
SELECT
 'ALTER TABLE [' + o.[name] + '] DROP COLUMN [msrepl_tran_version];'
FROM
SYS.OBJECTS AS o
INNER JOIN SYS.COLUMN AS c
ON o.OBJECT_ID = c.OBJECT_ID
AND [name] = 'msrepl_tran_version'
WHERE
o.type = 'u'