IF OBJECT_ID (N'DBO.FNC_DML_SEL') IS NOT NULL
    DROP FUNCTION DBO.FNC_DML_SEL;
GO


CREATE FUNCTION [DBO].[FNC_DML_SEL]( @P_TABLE_NAME  VARCHAR(MAX)
                                   , @P_ALIAS       VARCHAR(MAX))
RETURNS TABLE
AS
RETURN 
(
    SELECT  T.COL1
    ,       T.COL2
    ,       T.COL3
    ,       T.COL4
    ,       CASE 
                --WHEN T.TABLE_SCHEMA IS NOT NULL THEN T.TABLE_SCHEMA
                WHEN T.TABLE_SCHEMA IS NOT NULL THEN NULL
                ELSE CAST(ROW_NUMBER() OVER (ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, T.COLUMN_NO) AS VARCHAR(30))
            END     AS TABLE_SCHEMA

    FROM   (SELECT  LEFT(   CASE COL.ORDINAL_POSITION
                                WHEN 1 THEN 'SELECT'
                                ELSE ','
                            END + SPACE(8), 8)                                              AS COL1
            ,       LEFT(   CONCAT  ( @P_ALIAS
                                    , CASE WHEN @P_ALIAS IS NOT NULL THEN '.' ELSE '' END
                                    , COL.COLUMN_NAME
                                    , SPACE(40)), 40)                                       AS COL2
            ,       LEFT('AS ' + COL.COLUMN_NAME + SPACE(40), 40)                           AS COL3
            ,       CONCAT  ( '--'
                            , RTRIM(CONCAT(SPACE(1), CST.PK))
                            , CONCAT(SPACE(1), CAST(CMT.VALUE AS VARCHAR(MAX)))
                            , RTRIM(CONCAT(SPACE(1), CST.AK))
                            , RTRIM(CONCAT(SPACE(1), CST.FK)))                              AS COL4
            ,       COL.TABLE_SCHEMA                                                        AS TABLE_SCHEMA
            ,       COL.TABLE_NAME                                                          AS TABLE_NAME
            ,       COL.ORDINAL_POSITION                                                    AS COLUMN_NO

            FROM INFORMATION_SCHEMA.COLUMNS         COL
            JOIN SYS.ALL_COLUMNS                    CLU ON  CLU.OBJECT_ID   = OBJECT_ID(COL.TABLE_SCHEMA + '.' + COL.TABLE_NAME)
                                                        AND CLU.NAME        = COL.COLUMN_NAME

            LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CMT ON  CMT.MAJOR_ID    = CLU.OBJECT_ID
                                                        AND CMT.MINOR_ID    = CLU.COLUMN_ID
                                                        AND CMT.CLASS       = 1

            LEFT OUTER JOIN (SELECT T.TABLE_ID
                            ,       T.COLUMN_ID
                            ,       MIN(CASE T.TYPE WHEN 'PK' THEN 'Y'                              END)    AS IS_PK
                            ,       MIN(CASE T.TYPE WHEN 'PK' THEN 'PK(' + T.POSITION + ')'         END)    AS PK
                            ,       MIN(CASE T.TYPE WHEN 'UQ' THEN 'Y'                              END)    AS IS_AK
                            ,       STUFF( (SELECT  ', ' + CST.NAME + '(' + CAST(IDC.INDEX_COLUMN_ID AS VARCHAR(MAX)) + ')'
                                            FROM SYS.KEY_CONSTRAINTS    CST
                                            JOIN SYS.INDEX_COLUMNS      IDC ON  IDC.OBJECT_ID   = CST.PARENT_OBJECT_ID
                                                                            AND IDC.INDEX_ID    = CST.UNIQUE_INDEX_ID
                                            WHERE CST.PARENT_OBJECT_ID  = T.TABLE_ID
                                            AND IDC.COLUMN_ID           = T.COLUMN_ID
                                            AND CST.TYPE                = 'UQ'
                                            FOR XML PATH('')), 1, 2, '')                                    AS AK


                            ,       MIN(CASE T.TYPE WHEN 'F'  THEN 'Y'                              END)    AS IS_FK
                            ,       STUFF( (SELECT  ', ' + FKY.NAME + '(' + CAST(FKC.CONSTRAINT_COLUMN_ID AS VARCHAR(MAX)) + ')'
                                            FROM SYS.FOREIGN_KEYS           FKY
                                            JOIN SYS.FOREIGN_KEY_COLUMNS    FKC ON FKC.CONSTRAINT_OBJECT_ID = FKY.OBJECT_ID
                                            WHERE FKY.PARENT_OBJECT_ID  = T.TABLE_ID
                                            AND FKC.PARENT_COLUMN_ID    = T.COLUMN_ID
                                            FOR XML PATH('')), 1, 2, '')                                    AS FK

                            FROM   (SELECT  CST.NAME
                                    ,       CST.TYPE
                                    ,       CST.PARENT_OBJECT_ID                            AS TABLE_ID
                                    ,       IDC.COLUMN_ID                                   AS COLUMN_ID
                                    ,       CAST(IDC.INDEX_COLUMN_ID AS VARCHAR(MAX))       AS POSITION

                                    FROM SYS.KEY_CONSTRAINTS    CST
                                    JOIN SYS.INDEX_COLUMNS      IDC ON  IDC.OBJECT_ID   = CST.PARENT_OBJECT_ID
                                                                    AND IDC.INDEX_ID    = CST.UNIQUE_INDEX_ID
                                    UNION ALL

                                    SELECT  FKY.NAME
                                    ,       FKY.TYPE
                                    ,       FKY.PARENT_OBJECT_ID                            AS TABLE_ID
                                    ,       FKC.PARENT_COLUMN_ID                            AS COLUMN_ID
                                    ,       CAST(FKC.CONSTRAINT_COLUMN_ID AS VARCHAR(MAX))  AS POSITION

                                    FROM SYS.FOREIGN_KEYS           FKY
                                    JOIN SYS.FOREIGN_KEY_COLUMNS    FKC ON FKC.CONSTRAINT_OBJECT_ID = FKY.OBJECT_ID
                                    ) T

                            GROUP BY    T.TABLE_ID
                            ,           T.COLUMN_ID
                            ) CST   ON  CST.TABLE_ID    = CLU.OBJECT_ID
                                    AND CST.COLUMN_ID   = CLU.COLUMN_ID

            UNION ALL

            SELECT  LEFT('FROM ' + SPACE(8), 8)                                         AS COL1
            ,       LEFT(CONCAT(TBL.TABLE_NAME, SPACE(1), @P_ALIAS, SPACE(40)), 40)     AS COL2
            ,       ''                                                                  AS COL3
            ,       '-- ' + CAST(CMT.VALUE AS VARCHAR(MAX))                             AS COL4
            ,       TBL.TABLE_SCHEMA                                                    AS TABLE_SCHEMA
            ,       TBL.TABLE_NAME                                                      AS TABLE_NAME
            ,       9999                                                                AS COLUMN_NO
            FROM INFORMATION_SCHEMA.TABLES TBL
            LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CMT ON  CMT.MAJOR_ID = OBJECT_ID(TBL.TABLE_SCHEMA + '.'+ TBL.TABLE_NAME)
                                                        AND CMT.MINOR_ID = 0
            ) T
    WHERE T.TABLE_NAME = @P_TABLE_NAME
);
GO