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
Like this:
Like Loading...
Related