IF OBJECT_ID('TableSchemaToXML') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('TableSchemaToXML'), N'IsProcedure') = 1
    DROP PROCEDURE TableSchemaToXML
GO

CREATE PROCEDURE TableSchemaToXML(@TABLE_NAME NVARCHAR(256)) AS
    SET NOCOUNT ON
    
--    DECLARE @TABLE_NAME                 NVARCHAR(256)
    DECLARE @COLUMN_NAME                NVARCHAR(256)
    DECLARE @ORDINAL_POSITION           VARCHAR(256)
    DECLARE @COLUMN_DEFAULT             VARCHAR(256)
    DECLARE @IS_NULLABLE                VARCHAR(256)
    DECLARE @DATA_TYPE                  VARCHAR(256)
    DECLARE @CHARACTER_MAXIMUM_LENGTH   VARCHAR(256)
    DECLARE @NUMERIC_PRECISION          VARCHAR(256)
    DECLARE @NUMERIC_SCALE              VARCHAR(256)
    DECLARE @PRIMARY_KEY                VARCHAR(256)
    DECLARE @FOREIGN_KEY                VARCHAR(256)
    DECLARE @TAG_TYPE                   BIT
    
--    SELECT @TABLE_NAME = TABLE_NAME
--      FROM INFORMATION_SCHEMA.TABLES
--     WHERE TABLE_TYPE = 'BASE TABLE'
--       AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N'IsUserTable') = 1
    
    PRINT '<table name="' + @TABLE_NAME + '">'
    
    SELECT *
      INTO #TABLE_COLUMNS
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = @TABLE_NAME
    --SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'tworkflow'
 
    WHILE (SELECT COUNT(*) FROM #TABLE_COLUMNS) > 0
        BEGIN
            SELECT @COLUMN_NAME = COLUMN_NAME,
                   @ORDINAL_POSITION = CONVERT(VARCHAR, ORDINAL_POSITION),
                   @COLUMN_DEFAULT = 
                       CASE
                           WHEN COLUMN_DEFAULT IS NULL THEN ''
                           ELSE CHAR(10) + CHAR(9) + '        default="' + COLUMN_DEFAULT + '"'
                       END,
                   @IS_NULLABLE =
                       CASE UPPER(IS_NULLABLE)
                           WHEN 'YES' THEN 'true'
                           ELSE 'false'
                       END,
                   @DATA_TYPE = DATA_TYPE,
                   @CHARACTER_MAXIMUM_LENGTH =
                       CASE
                           WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
                           ELSE CHAR(10) + CHAR(9) + '        length="' + CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) + '"'
                       END,
                   @NUMERIC_PRECISION =
                       CASE
                           WHEN NUMERIC_PRECISION IS NULL THEN ''
                           ELSE CHAR(10) + CHAR(9) + '        precision="' + CONVERT(VARCHAR, NUMERIC_PRECISION) + '"'
                       END,
                   @NUMERIC_SCALE =
                       CASE
                           WHEN NUMERIC_SCALE IS NULL THEN ''
                           WHEN NUMERIC_SCALE > 0 THEN CHAR(10) + CHAR(9) + '        numeric-scale="' + CONVERT(VARCHAR, NUMERIC_SCALE) + '"'
                           ELSE ''
                       END
              FROM #TABLE_COLUMNS
             WHERE ORDINAL_POSITION = (SELECT MIN(ORDINAL_POSITION) FROM #TABLE_COLUMNS)
    
            SELECT *
              INTO #COLUMN_CONSTRAINTS
              FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
             WHERE TABLE_NAME = @TABLE_NAME
               AND COLUMN_NAME = @COLUMN_NAME
    
    --        IF (SELECT COUNT(*) FROM #COLUMN_CONSTRAINTS) > 0
    --            SET @TAG_TYPE = 0
    --        ELSE
                SET @TAG_TYPE = 1
    
            IF EXISTS(SELECT * FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsPrimaryKey') = 1)
                SET @PRIMARY_KEY = CHAR(10) + CHAR(9) + '        primary-key="true"'
            ELSE
                SET @PRIMARY_KEY = ''
    
            IF EXISTS(SELECT * FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
                SELECT @FOREIGN_KEY = CHAR(10) + CHAR(9) + '        foreign-key-table="' + OBJECT_NAME(sysreferences.rkeyid) + '"' + CHAR(10) +
                                      CHAR(9) + '        foreign-key-column="' + INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME + '"'
                  FROM sysreferences,
                       INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                 WHERE sysreferences.constid = (SELECT OBJECT_ID(CONSTRAINT_NAME) FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
                   AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
            ELSE
                SET @FOREIGN_KEY = ''
 
            PRINT CHAR(9) + '<column name="' + @COLUMN_NAME + '"' + CHAR(10) +
                  CHAR(9) + '        position="' + @ORDINAL_POSITION + '"' + 
                  @COLUMN_DEFAULT + CHAR(10) +
                  CHAR(9) + '        nullable="' + @IS_NULLABLE + '"' + CHAR(10) +
                  CHAR(9) + '        data-type="' + @DATA_TYPE + '"' + 
                  @CHARACTER_MAXIMUM_LENGTH +
                  @NUMERIC_PRECISION + 
                  @NUMERIC_SCALE +
                  @PRIMARY_KEY +
                  @FOREIGN_KEY +
                  CASE @TAG_TYPE WHEN 1 THEN '/>' ELSE '>' END
    
            DROP TABLE #COLUMN_CONSTRAINTS
    
            DELETE
              FROM #TABLE_COLUMNS
             WHERE COLUMN_NAME = @COLUMN_NAME
        END
    
    DROP TABLE #TABLE_COLUMNS
    
    PRINT '</table>'
GO