SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE   PROCEDURE [dbo].[p_sys_fs_GetSourceControlledObjects]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ServerName nvarchar(256) =
        CONVERT(nvarchar(256), SERVERPROPERTY('ServerName'));

    DECLARE @DatabaseName sysname = DB_NAME();

    DECLARE @LinkedDatabasesFile nvarchar(4000);
    DECLARE @Xml xml;
    DECLARE @Sql nvarchar(max);
    DECLARE @WorkingBase nvarchar(4000);

    ----------------------------------------------------------------
    -- Locate LinkedDatabases.xml
    ----------------------------------------------------------------

    BEGIN TRY

        EXEC dbo.p_sys_fs_GetLinkedDatabasesFile
            @FileName = @LinkedDatabasesFile OUTPUT;

    END TRY
    BEGIN CATCH

        RAISERROR(
            'SQL Source Control information is not available. Reference database objects were not refreshed.',
            10,
            1
        );

        RETURN;

    END CATCH;

    ----------------------------------------------------------------
    -- Read LinkedDatabases.xml
    ----------------------------------------------------------------

    BEGIN TRY

        SET @Sql = N'
            SELECT @Xml = BulkColumn
            FROM OPENROWSET(
                BULK ''' + REPLACE(@LinkedDatabasesFile, '''', '''''') + N''',
                SINGLE_NCLOB
            ) AS F;';

        EXEC sys.sp_executesql
            @Sql,
            N'@Xml xml OUTPUT',
            @Xml OUTPUT;

    END TRY
    BEGIN CATCH

        RAISERROR(
            'SQL Source Control configuration could not be read. Reference database objects were not refreshed.',
            10,
            1
        );

        RETURN;

    END CATCH;

    ----------------------------------------------------------------
    -- Find WorkingBase for current database
    ----------------------------------------------------------------

    SELECT @WorkingBase =
        X.N.value(
            '(IWorkspaceId/RootPath/text())[1]',
            'nvarchar(4000)'
        )
    FROM @Xml.nodes(
        '/LinkedDatabaseStore/LinkedDatabaseList/value'
    ) AS X(N)
    WHERE X.N.value(
              '(DatabaseId/ServerAndInstanceName/text())[1]',
              'nvarchar(256)'
          ) = @ServerName
      AND X.N.value(
              '(DatabaseId/DatabaseName/text())[1]',
              'sysname'
          ) = @DatabaseName;

    IF @WorkingBase IS NULL
    BEGIN
        RAISERROR(
            'Current database is not linked to SQL Source Control. Reference database objects were not refreshed.',
            10,
            1
        );

        RETURN;
    END;

    ----------------------------------------------------------------
    -- Candidate objects
    ----------------------------------------------------------------

    CREATE TABLE #SourceControlledObjects
    (
        SchemaName            sysname        NOT NULL,
        ObjectName            sysname        NOT NULL,
        ObjectType            varchar(2)     NOT NULL,
        ObjectTypeDescription varchar(60)    NOT NULL,
        FileName              nvarchar(4000) NOT NULL
    );

    ----------------------------------------------------------------
    -- Tables, views, procedures and functions
    ----------------------------------------------------------------

    INSERT INTO #SourceControlledObjects
    (
        SchemaName,
        ObjectName,
        ObjectType,
        ObjectTypeDescription,
        FileName
    )
    SELECT
        s.name,
        o.name,
        o.type,
        o.type_desc,

        @WorkingBase
        + N'\'
        + CASE o.type
            WHEN 'U'  THEN N'Tables'
            WHEN 'V'  THEN N'Views'
            WHEN 'P'  THEN N'Stored Procedures'
            WHEN 'FN' THEN N'Functions'
            WHEN 'IF' THEN N'Functions'
            WHEN 'TF' THEN N'Functions'
          END
        + N'\'
        + s.name
        + N'.'
        + o.name
        + N'.sql'
    FROM sys.objects o
    INNER JOIN sys.schemas s
        ON s.schema_id = o.schema_id
    WHERE o.type IN ('U', 'V', 'P', 'FN', 'IF', 'TF');

    ----------------------------------------------------------------
    -- User-defined data types, including table types
    ----------------------------------------------------------------

    INSERT INTO #SourceControlledObjects
    (
        SchemaName,
        ObjectName,
        ObjectType,
        ObjectTypeDescription,
        FileName
    )
    SELECT
        s.name,
        t.name,
        'UD',
        CASE
            WHEN tt.user_type_id IS NOT NULL
                THEN 'USER_TABLE_TYPE'
            ELSE 'USER_DEFINED_TYPE'
        END,

        @WorkingBase
        + N'\Types\User-defined Data Types\'
        + s.name
        + N'.'
        + t.name
        + N'.sql'
    FROM sys.types t
    INNER JOIN sys.schemas s
        ON s.schema_id = t.schema_id
    LEFT JOIN sys.table_types tt
        ON tt.user_type_id = t.user_type_id
    WHERE t.is_user_defined = 1;

    ----------------------------------------------------------------
    -- Determine which objects actually have a Source Control file
    ----------------------------------------------------------------

    CREATE TABLE #Result
    (
        SchemaName            varchar(128) NOT NULL,
        ObjectName            varchar(128) NOT NULL,
        ObjectType            varchar(2)   NOT NULL,
        ObjectTypeDescription varchar(60)  NOT NULL
    );

    DECLARE
        @SchemaName sysname,
        @ObjectName sysname,
        @ObjectType varchar(2),
        @ObjectTypeDescription nvarchar(60),
        @FileName nvarchar(4000),
        @Exists bit;

    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
        SELECT
            SchemaName,
            ObjectName,
            ObjectType,
            ObjectTypeDescription,
            FileName
        FROM #SourceControlledObjects
        ORDER BY
            ObjectType,
            SchemaName,
            ObjectName;

    OPEN c;

    FETCH NEXT FROM c
        INTO
            @SchemaName,
            @ObjectName,
            @ObjectType,
            @ObjectTypeDescription,
            @FileName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Exists = 0;

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

        IF @Exists = 1
        BEGIN
            INSERT INTO #Result
            (
                SchemaName,
                ObjectName,
                ObjectType,
                ObjectTypeDescription
            )
            VALUES
            (
                @SchemaName,
                @ObjectName,
                @ObjectType,
                @ObjectTypeDescription
            );
        END;

        FETCH NEXT FROM c
            INTO
                @SchemaName,
                @ObjectName,
                @ObjectType,
                @ObjectTypeDescription,
                @FileName;
    END;

    CLOSE c;
    DEALLOCATE c;

    ----------------------------------------------------------------
    -- Replace reference data
    --
    -- The persistent table is changed only after the complete
    -- Source Control scan has succeeded.
    ----------------------------------------------------------------

    BEGIN TRY

        BEGIN TRANSACTION;

        DELETE FROM dbo.nm_reference_database_objects;

        INSERT INTO dbo.nm_reference_database_objects
        (
            nrdo_SchemaName,
            nrdo_ObjectName,
            nrdo_ObjectType,
            nrdo_ObjectTypeDescription
        )
        SELECT
            SchemaName,
            ObjectName,
            ObjectType,
            ObjectTypeDescription
        FROM #Result;

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        THROW;

    END CATCH;

    ----------------------------------------------------------------
    -- Return the generated reference data
    ----------------------------------------------------------------

    SELECT
        nrdo_SchemaName,
        nrdo_ObjectName,
        nrdo_ObjectType,
        nrdo_ObjectTypeDescription
    FROM dbo.nm_reference_database_objects
    ORDER BY
        nrdo_ObjectType,
        nrdo_SchemaName,
        nrdo_ObjectName;
END;
GO
