[Documenting]結合Table Layout與Value

        現有個需求是製作Table Layout的文件，有個比較特別的是還需要列出一筆對應的欄位值，產生Table Layout很簡單，列出一筆資料也很簡單，結合的話可能得用到Execl，把那一筆資料轉置，我想把這幾個步驟自動化，之後如果要製作所有的資料表時就會方便很囉了

DECLARE @UnpivotColHeader NVARCHAR(MAX)

        ,@UnpivotTableSQL NVARCHAR(MAX)

                     ,@UnpivotColValue NVARCHAR(MAX)

                     ,@CteSQL NVARCHAR(MAX)

                     ,@execSQL NVARCHAR(MAX);

DECLARE @SchemaName NVARCHAR(128)

                     ,@TableName NVARCHAR(128);

SELECT @SchemaName = N'HumanResources',@TableName = N'Department';

SELECT @UnpivotColValue =

           isnull(@UnpivotColValue + ',' + (CASE WHEN DATA_TYPE = 'image' THEN 'convert(nvarchar(255),convert(varbinary(255),' + COLUMN_NAME + ')) ''' + COLUMN_NAME + ''''  ELSE 'ISNULL(CAST([' + COLUMN_NAME + '] AS NVARCHAR(255)),'''') ''' + COLUMN_NAME + '''' END)

                                                                                                ,(CASE WHEN DATA_TYPE = 'image' THEN 'convert(nvarchar(255),convert(varbinary(255),' + COLUMN_NAME + ')) ''' + COLUMN_NAME + '''' ELSE 'ISNULL(CAST([' + COLUMN_NAME + '] AS NVARCHAR(255)),'''') ''' + COLUMN_NAME + '''' END))

              FROM INFORMATION_SCHEMA.COLUMNS

                     WHERE TABLE_SCHEMA = @SchemaName

                            AND TABLE_NAME = @TableName;

--SELECT @UnpivotColValue

SELECT @UnpivotColHeader =  --將列標題轉換為行

    STUFF(

        (SELECT N','+ QUOTENAME(x) AS [text()]

       FROM (

               SELECT COLUMN_NAME AS x

              FROM INFORMATION_SCHEMA.COLUMNS

                     WHERE TABLE_SCHEMA = @SchemaName

                            AND TABLE_NAME = @TableName

                 ) AS x

        FOR XML PATH('')),  

        1,1,N'');

--SELECT @UnpivotColHeader;

SET @UnpivotTableSQL =

           'SELECT ColumnName ,ColumnValue

           FROM (

       SELECT *

                 FROM (       

                 SELECT TOP(1) ' + @UnpivotColValue +

                 '          FROM [' + @SchemaName + '].[' + @TableName + ']) AS [' +  @TableName + ']

                 UNPIVOT

                            (

                            ColumnValue FOR ColumnName IN (' + @UnpivotColHeader + ')

                            )  x     

    )y' + N'';

--PRINT @UnpivotTableSQL;

set @CteSQL =

           'WITH CTE

           AS

           (

           SELECT

             Col.TABLE_NAME

           , Col.COLUMN_NAME

           , DataType = CAST(DATA_TYPE AS NVARCHAR(50))  + ISNULL( ''('' + CAST(CHARACTER_MAXIMUM_LENGTH  AS NVARCHAR(50)) + '')'', '''')

           , Const.CONSTRAINT_NAME

           , TblCon.CONSTRAINT_TYPE

           , Col.IS_NULLABLE

           , ch.CHECK_CLAUSE

           , Col.COLUMN_DEFAULT

           , [Is_Identity] = CASE WHEN SysCol.is_identity = 1 THEN ''Yes'' ELSE ''No'' END

           FROM INFORMATION_SCHEMA.COLUMNS AS Col

           LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Const

             ON Col.TABLE_NAME  = Const.TABLE_NAME

             AND Col.COLUMN_NAME  = Const.COLUMN_NAME

           LEFT JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS Ch

             ON Ch.CONSTRAINT_NAME  = Const.CONSTRAINT_NAME

           LEFT JOIN INFORMATION_SCHEMA.Table_CONSTRAINTS AS TblCon

             ON TblCon.CONSTRAINT_NAME  = Const.CONSTRAINT_NAME

           LEFT JOIN sys.all_columns AS SysCol

             ON OBJECT_NAME(SysCol.[Object_id]) = Const.TABLE_NAME

             AND SysCol.NAME  = Const.COLUMN_NAME

           WHERE Col.TABLE_NAME =''' + @TableName + '''

           --Order By  Col.TABLE_NAME , Col.ORDINAL_POSITION

           )

           SELECT

                       C.TABLE_NAME

                     , C.COLUMN_NAME

                     , C.DataType

                     , Result.ColumnValue

                     , C.CONSTRAINT_NAME

                     , C.CONSTRAINT_TYPE

                     , C.IS_NULLABLE

                     , C.CHECK_CLAUSE

                     , C.COLUMN_DEFAULT

                     , C.[Is_Identity] 

           FROM CTE C LEFT OUTER JOIN (' + @UnpivotTableSQL + ') Result

           ON C.COLUMN_NAME = Result.ColumnName ';

--PRINT @CteSQL;

EXEC sp_executesql @CteSQL;

        這個並沒有很完美，應該有bug， 不過也夠我用了，如有問題請提出