ADD RowGUID column to all Tables in a DB even with multiple schemas

If you are setting a database for HA this will be a needed function and better to setup before hand otherwise SQL server will add the columns. You take a performance hit on your server during setup of HA or during Merge operations if SQL Server does not find a uniqueidentifier column setup as a RowGUID Identity.

This script will get all the user tables and their schemas in a DB and build the ALTER statement needed to ADD the RowGUID column to each of th tables.

The orginal Code came from [link http://sqlmag.com/blog/preemptive-rowguidcols-merge-replicated-databases]
Preemptive RowGuidCols on Merge Replicated Databases
by Michael K. Campbell in Practical SQL Server
Oct. 12, 2011[/link]


I just tweeked it to handle a database with multiple schemas.

[code T-SQL]
DECLARE rowguider CURSOR FAST_FORWARD
FOR
SELECT
[object_id],
[TABLE_SCHEMA],
[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
INNER JOIN [sys].[all_objects] ON [INFORMATION_SCHEMA].[TABLES].TABLE_NAME = [sys].[all_objects].[name]
WHERE [sys].[all_objects].[type] IN ('U', 'V')
AND [sys].[all_objects].[is_ms_shipped] <> 1
AND [sys].[all_objects].[name] <> 'sysdiagrams'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME]

DECLARE @tableId INT
DECLARE @tableSchema SYSNAME
DECLARE @tableName SYSNAME
DECLARE @rowguidcol BIT
DECLARE @sql NVARCHAR(300)

OPEN rowguider

FETCH NEXT
FROM rowguider
INTO @tableId, @tableSchema, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowguidcol = 0

-- see if there's a rowguid col:
SELECT @rowguidcol = ISNULL(is_rowguidcol, 0)
FROM sys.columns
WHERE object_id = @tableId
AND is_rowguidcol = 1

IF @rowguidcol = 0
BEGIN
SET @sql = 'ALTER TABLE [' + @tableSchema + '].' + '[' + @tableName + ']' +
' ADD RowGUID uniqueidentifier RowGUIDCol ' +
'CONSTRAINT [DF_' + @tableSchema + '_' + @tableName + '_RowGUIDCol] ' +
'DEFAULT NEWSEQUENTIALID() NOT NULL'

PRINT @sql

-- Uncomment to automaticaly ADD the RowGUID columns to your Tables
-- EXECUTE sp_executesql @sql
END

FETCH NEXT
FROM rowguider
INTO @tableId, @tableSchema, @tableName

END

CLOSE rowguider

DEALLOCATE rowguider
GO
[/code]