"팁" 태그로 연결된 1개 게시물개의 게시물이 있습니다.

CREATE PROCEDURE [dbo].[ExportSchemaInstruction]
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX) = ''
    DECLARE @Content NVARCHAR(MAX) = ''

    -- 헤더 정보 추가
    SET @Content = @Content + '- SQL Server 2016 이상에서 실행하는 SQL 을 만들어 줘' + CHAR(13) + CHAR(10)
    SET @Content = @Content + '- 가능하면 SQL 은 TSV: Tab Separated Values 로 구성된 `tsv 테이블 스키마`, `tsv 테이블 관계`를 기준으로 SqlServer 에서 실행하는 SQL 로 만들어 줘' + CHAR(13) + CHAR(10)
    SET @Content = @Content + '- 테이블 명, 컬럼 명 표기법은 Pascal Case 로 만들어 줘' + CHAR(13) + CHAR(10)
    SET @Content = @Content + '- 테이블 명 의 별칭은 Pascal Case 각 첫 단어의 조합으로 만들어 줘' + CHAR(13) + CHAR(10)
    SET @Content = @Content + '- 결과 SQL 은 언제나 Full 쿼리로 알려주고, 예제로 제공되는 SQL 에 대한 주석은 만들지 마' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    
    -- 테이블 정보 섹션
    SET @Content = @Content + '``` tsv 테이블 정보' + CHAR(13) + CHAR(10)
    
    DECLARE @TableData NVARCHAR(MAX) = ''
    SELECT @TableData = (
        SELECT 
            CAST(t.name AS VARCHAR(256)) + CHAR(9) +
            ISNULL(CAST(ep.value AS NVARCHAR(4000)), '') + CHAR(9) +
            CHAR(13) + CHAR(10)
        FROM 
            sys.tables t
            LEFT JOIN sys.extended_properties ep 
                ON ep.major_id = t.object_id 
                AND ep.minor_id = 0 
                AND ep.name = 'MS_Description'
        ORDER BY 
            t.name
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')

    SET @Content = @Content + @TableData + '```' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

    -- 테이블 스키마 섹션
    SET @Content = @Content + '``` tsv 테이블 스키마' + CHAR(13) + CHAR(10)
    SET @Content = @Content + 'TableName' + CHAR(9) + 'ColumnName' + CHAR(9) + 'DataType' + CHAR(9) + 'Length' + CHAR(9) + 'Nullable' + CHAR(9) + 'PrimaryKey' + CHAR(9) + 'DefaultValue' + CHAR(9) + 'ColumnDescription' + CHAR(13) + CHAR(10)
    SET @Content = @Content + '---' + CHAR(9) + '---' + CHAR(9) + '---' + CHAR(9) + '---' + CHAR(9) + '---' + CHAR(9) + '---' + CHAR(9) + '---' + CHAR(9) + '---' + CHAR(13) + CHAR(10)
    
    DECLARE @SchemaData NVARCHAR(MAX) = ''
    SELECT @SchemaData = (
        SELECT 
            C.TABLE_NAME + CHAR(9) +
            C.COLUMN_NAME + CHAR(9) +
            C.DATA_TYPE + CHAR(9) +
            ISNULL(CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)), '-1') + CHAR(9) +
            LEFT(C.IS_NULLABLE, 1) + CHAR(9) +
            CASE 
                WHEN KCU.COLUMN_NAME IS NOT NULL THEN 'Y'
                ELSE 'N'
            END + CHAR(9) +
            ISNULL(DC.definition, '') + CHAR(9) +
            ISNULL(CAST(EP.value AS NVARCHAR(MAX)), '') + 
            CHAR(13) + CHAR(10)
        FROM 
            INFORMATION_SCHEMA.COLUMNS AS C
            LEFT JOIN sys.extended_properties AS EP 
                ON EP.major_id = OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME))
                AND EP.minor_id = (
                    SELECT column_id 
                    FROM sys.columns 
                    WHERE object_id = OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME))
                        AND name = C.COLUMN_NAME
                )
                AND EP.name = 'MS_Description'
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
                ON C.TABLE_NAME = KCU.TABLE_NAME 
                AND C.COLUMN_NAME = KCU.COLUMN_NAME
                AND C.TABLE_SCHEMA = KCU.TABLE_SCHEMA
                AND KCU.CONSTRAINT_NAME IN (
                    SELECT CONSTRAINT_NAME 
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                    WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
                )
            LEFT JOIN sys.default_constraints AS DC
                ON DC.parent_object_id = OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME))
                AND DC.parent_column_id = (
                    SELECT column_id 
                    FROM sys.columns 
                    WHERE object_id = OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME))
                        AND name = C.COLUMN_NAME
                )
        ORDER BY 
            C.TABLE_NAME,
            C.ORDINAL_POSITION
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)');
    
    SET @Content = @Content + @SchemaData + '```' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    
    -- 테이블 관계 섹션
    SET @Content = @Content + '``` tsv 테이블 관계' + CHAR(13) + CHAR(10)
    
    DECLARE @RelationData NVARCHAR(MAX) = ''
    SELECT @RelationData = @RelationData + 
        CAST(ModelGroupID AS NVARCHAR(50)) + CHAR(9) +
        CAST(DepartureTableID AS VARCHAR(50)) + CHAR(9) +
        CAST(DepartureFieldID AS VARCHAR(50)) + CHAR(9) +
        CAST(DepartureFlowSymbol AS VARCHAR(50)) + CHAR(9) +
        CAST(FlowLineType AS VARCHAR(50)) + CHAR(9) +
        CAST(ArrivalFlowSymbol AS VARCHAR(50)) + CHAR(9) +
        CAST(ArrivalTableID AS VARCHAR(50)) + CHAR(9) +
        CAST(ArrivalFieldID AS VARCHAR(50)) + CHAR(9) +
        CHAR(13) + CHAR(10)
    FROM ModelRelation 
    ORDER BY ModelGroupID
    
    SET @Content = @Content + @RelationData + '```' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    
    -- 기초코드 목록 섹션
    SET @Content = @Content + '``` tsv 기초코드 목록' + CHAR(13) + CHAR(10)
    SET @Content = @Content + 'BaseCodeID' + CHAR(9) + 'BaseCodeText' + CHAR(13) + CHAR(10)
    SET @Content = @Content + '---' + CHAR(9) + '---' + CHAR(13) + CHAR(10)
    
    DECLARE @BaseCodeData NVARCHAR(MAX) = ''
    SELECT @BaseCodeData = (
        SELECT 
            CAST(CodeID AS VARCHAR(50)) + CHAR(9) +
            CAST(CodeValue AS NVARCHAR(MAX)) + 
            CHAR(13) + CHAR(10)
        FROM BaseCode 
        WHERE GroupCode = 'SYS000'
        ORDER BY CodeID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    
    SET @Content = @Content + @BaseCodeData + '```' + CHAR(13) + CHAR(10)
    
    SELECT @Content AS TextContent
END