Working with SharePoint, Project Server or Dynamics databases, or any other database for that matter sometimes involves replacing GUIDs. Although it is not supported in a production environment, it can be a useful action in a development environment.
The issue with many of these application managed databases is that the use of a particular GUID is not easy to understand prior to making a change and if all references are not changed then the application relying on the database is effectively broken.
Approach
The approach that I have used is write a SQL statement to update a single column in a table and then use that as a template to build a set of statements to update every column that contains a uniqueidentifier data type.
Within the template SQL, there are tokens that will be replaced with the old GUID and the new GUID, and the schema, table and column names.
A list of all columns with the uniqueidentifier data type is retrieved from the sys.columns table and this information is used to create an instance of the SQL statement for each column.
The output is a SQL statement for each column in the database with the uniqueidentifier data type that will replace the old GUID with the new GUID.
The Script
The variables are defined, including the the old and new GUIDs. The GUIDs should contain the values to use in the replace.
The @SQLTemplate variable is used to generate the update script for each column and the tokens surrounded by { and } will be replaced. Each instance of the replaced @SQLTemplate statement will be added to @SQLCommand which in turn will be executed to update the columns.
/**************************************************
Declare local variables
**************************************************/
DECLARE
@OldGUIDValue VARCHAR(36) = ''
, @NewGUIDValue VARCHAR(36) = ''
, @SQLCommand NVARCHAR(MAX) = ''
, @SQLTemplate NVARCHAR(1024) = 'UPDATE [{SchemaName}].[{TableName}] SET {ColumnName} = ''{NewGUIDValue}'' WHERE {ColumnName} = ''{OldGUIDValue}'''
;
The following query uses a CTE to retrieve the columns that need to be updated, along with the other information required to carry out the replacement of the tokens in @SQLTemplate. The output of the CTE is then used in a replace statement that adds each replaced output to the @SQLCommand variable.
/**************************************************/
PRINT 'INFO: Building Query';
/**************************************************/
;WITH cteFields (
SchemaName
, TableName
, ColumnName
) AS (
SELECT
OBJECT_SCHEMA_NAME(C.object_id) AS [SchemaName]
, OBJECT_NAME(C.object_id) AS [TableName]
, C.name AS [ColumnName]
FROM sys.columns C
JOIN sys.tables T ON (T.object_id = C.object_id)
WHERE
system_type_id = 36
AND
OBJECT_SCHEMA_NAME(C.object_id) NOT IN ('sys')
)
SELECT
@SQLCommand = @SQLCommand + '
' +
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SQLTemplate, '{NewGUIDValue}', @NewGUIDValue), '{OldGUIDValue}', @OldGUIDValue), '{ColumnName}', F.ColumnName), '{TableName}', F.TableName), '{SchemaName}', F.SchemaName)
FROM cteFields F
;
Finally, the content of @SQLCommand is executed to carry out the update.
/**************************************************/
PRINT 'INFO: Executing Query';
/**************************************************/
EXEC(@SQLCommand);
SELECT @Rowcount = @@ROWCOUNT
IF @Rowcount = 0
BEGIN
PRINT 'INFO: ' + @OldGUIDValue + ' not found';
END
A Note of Caution
This script will update the whole of a database so if you are unsure of the outcome of this type of update, make sure that you back up your database first.