NAV2015 upgrade step-2

This function replaces occurences of the "User ID" field with "Domain\User ID". If you look in a 2009 database, the "User setup" table has user id's without domain prefix(es). In 2013 and later, it has the domain prefix.

Ron

We did it with a SQL-script...

--**************************************************************************************************************
-- Christer Berntsson, Softronic, Stockholm, Sweden, 2015-06-17
--
-- Idea from Lars Westman, Umeå, Sweden
--
-- Update all columns in NAV-2013--> with domainname
--
-- This script should be run in SQL-server Management Studio
--
-- 2015-06-17: In a Nav2015-database with 8 Companies and Size of 87GB on SSD-discs it took aprox 5 minutes.
--
-- Test it before You use it
--**************************************************************************************************************
USE [Nav2015_XYZ] -- <<-- Your database
GO

--Use a temporay table
CREATE TABLE [#User ID Fields] (Tablename varchar(250), Fieldname varchar(50), Size int)

--Fill temporay table with tablename and field
INSERT INTO [#User ID Fields] (Tablename, Fieldname, Size)
SELECT
o.name
,c.name
,c.max_length
FROM
sys.columns c
Join sys.objects o on o.object_id = c.object_id
WHERE
c.name like '%User_ID%'
and o.type_desc like 'USER_TABLE' --only usertables
and c.system_type_id = 231 --only textfields
-- add Your own filters and exceptions
ORDER BY
o.name

--DEV:
--select * from [#User ID Fields] -- Show table who contain '%User_ID%' and is text-fields

DECLARE @sql nvarchar(MAX),
@CompanyName nvarchar(254),
@Tablename nvarchar(254),
@Fieldname nvarchar(254),
@Domain nvarchar(254),
@Size int

SET @Domain ='TheDomainName'

--Start loop all Companies and Tables and Fields
DECLARE UpdateCursor CURSOR FORWARD_ONLY FOR
SELECT Tablename, Fieldname, Size FROM [#User ID Fields]

OPEN UpdateCursor
FETCH NEXT FROM UpdateCursor INTO @Tablename, @Fieldname, @Size

@FETCH_STATUS = 0
BEGIN
PRINT getdate()
SET @sql = 'UPDATE SET = CASE WHEN LEN()<>0 THEN '''+ @Domain+'\''+REVERSE(LEFT(REVERSE(),CHARINDEX(''\'',REVERSE(''\''+))-1)) ELSE END'
PRINT @sql
EXEC (@sql)
FETCH NEXT
FROM UpdateCursor
INTO @Tablename, @Fieldname, @Size
END

--Clean up
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
DROP TABLE [#User ID Fields]