SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE   PROCEDURE [dbo].[p_sys_fs_GetLinkedDatabasesFile]
    @FileName nvarchar(4000) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @FileName = NULL;

    DECLARE @LoginName nvarchar(256) = SUSER_SNAME();
    DECLARE @DomainName nvarchar(256);
    DECLARE @UserName nvarchar(256);

    DECLARE @Slash int = CHARINDEX(N'\', @LoginName);

    IF @Slash > 0
    BEGIN
        SET @DomainName = LEFT(@LoginName, @Slash - 1);
        SET @UserName = SUBSTRING(
            @LoginName,
            @Slash + 1,
            LEN(@LoginName)
        );
    END
    ELSE
    BEGIN
        SET @UserName = @LoginName;
        SET @DomainName = NULL;
    END;

    ---------------------------------------------------------------
    -- Candidate profile names
    --
    -- First:
    --     username
    --
    -- Then:
    --     domain.username
    ---------------------------------------------------------------

    DECLARE @ProfileNames TABLE
    (
        ProfileName nvarchar(256) NOT NULL
    );

    INSERT INTO @ProfileNames (ProfileName)
    VALUES (@UserName);

    IF @DomainName IS NOT NULL
    BEGIN
        INSERT INTO @ProfileNames (ProfileName)
        VALUES (@DomainName + N'.' + @UserName);
    END;

    ---------------------------------------------------------------
    -- Candidate drives
    ---------------------------------------------------------------

    DECLARE @Drives TABLE
    (
        Drive char(1) NOT NULL
    );

    INSERT INTO @Drives (Drive)
    VALUES
        ('C'),
        ('D'),
        ('E');

    ---------------------------------------------------------------
    -- Try combinations
    --
    -- user
    -- domain.user
    --
    -- on C:, D:, E:
    ---------------------------------------------------------------

    DECLARE
        @Drive char(1),
        @ProfileName nvarchar(256),
        @Candidate nvarchar(4000),
        @Exists bit;

    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
        SELECT
            D.Drive,
            P.ProfileName
        FROM @Drives D
        CROSS JOIN @ProfileNames P
        ORDER BY
            D.Drive,
            CASE
                WHEN P.ProfileName = @UserName THEN 1
                ELSE 2
            END;

    OPEN c;

    FETCH NEXT FROM c
        INTO @Drive, @ProfileName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Candidate =
            @Drive + N':\Users\'
            + @ProfileName
            + N'\AppData\Local\Red Gate\SQL Source Control 7\LinkedDatabases.xml';

        SET @Exists = 0;

        EXEC dbo.p_sys_fs_IsFile
            @FileName = @Candidate,
            @Exists = @Exists OUTPUT;

        IF @Exists = 1
        BEGIN
            SET @FileName = @Candidate;
            BREAK;
        END;

        FETCH NEXT FROM c
            INTO @Drive, @ProfileName;
    END;

    CLOSE c;
    DEALLOCATE c;

    ---------------------------------------------------------------
    -- Nothing found
    ---------------------------------------------------------------

    IF @FileName IS NULL
    BEGIN
        THROW 50001,
            'Cannot locate SQL Source Control LinkedDatabases.xml.',
            1;
    END;
END;
GO
