INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

ADD RowGUID column to all Tables in a DB even with multiple schemas by sparkbyte
Posted: 17 Oct 13

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

Preemptive RowGuidCols on Merge Replicated Databases

by Michael K. Campbell in Practical SQL Server

Oct. 12, 2011



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 

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close