Blog ala Vidar

One of our customers are moving from one hosting provider to another, and because of this we have to migrate their databases and stuff. While doing this, they’re also changing their domain name (in Active Directory), and since that wasn’t enough change, all users will have a new naming convention in their usernames. With this in mind I’ve created a SQL script that will take “OldUser” and “NewUser”, look for all columns that have NVARCHAR(128), which is not computed, and update all of them. To make it easier, I’ve disabled the triggers and then enabled them when the query is finished.

SET NOCOUNT ON

DECLARE @OldUser AS NVARCHAR(128)
DECLARE @NewUser AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128)
DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  @OldUser = 'OldDomain\NordnesVidar',
  @NewUser = 'NewDomain\NordVida'

SELECT
  O.name AS TableName, C.name AS ColumnName
    INTO #columns
    FROM sys.sysobjects AS O
    INNER JOIN sys.syscolumns AS C ON O.id = C.id
    WHERE O.xtype = 'U'
      AND O.name LIKE '[as]tbl%' -- Naming convention in Omega for tables
      AND C.xusertype = 231 --NVARCHAR
      AND C.length = 256 -- 128 (unicode)
      AND C.iscomputed = 0
      AND C.name NOT IN ('Domain')
    ORDER BY O.name, C.name

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @TableName = TableName,
      @ColumnName = ColumnName
        FROM #columns

    PRINT @TableName + ' - ' + @ColumnName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    SET @SQL = 'UPDATE [' + @TableName + '] '
    SET @SQL = @SQL + 'SET [' + @ColumnName + '] = ''' + @NewUser + ''' '
    SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] = ''' + @OldUser + ''''
    EXEC (@SQL)

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    DELETE
        FROM #columns
        WHERE TableName = @TableName
          AND ColumnName = @ColumnName
END

DROP TABLE #columns