'Export database schema into XML file

I would like to export whole database schema to XML file. Information there what are needed.

  1. tables - columns, data type, pk, fk,
  2. views - returned columns,
  3. functions and stored procedures - arguments, returned columns and data types.

I cannot find anything in Google, etc... Is anyone used to face similar problem?

sql-serverxmltsqlsql-server-2012


Solution 1:[1]

By this query you can get tables and views:

SELECT TABLE_NAME AS '@Name', CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'Table' ELSE 'View' END AS '@Type',
(
    SELECT Column_Name as '@Name',
            DATA_TYPE as '@DataType',
            case data_type 
                when 'nvarchar' 
                then CHARACTER_MAXIMUM_LENGTH 
                when 'varchar'  
                then CHARACTER_MAXIMUM_LENGTH
                else null 
            end  as '@Length',
            IS_NULLABLE AS '@IsNullable',
            COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS '@IsIdentity',

            (SELECT tc.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND cu.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.Column_Name) AS '@Constraint'

    FROM INFORMATION_SCHEMA.COLUMNS 
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME
    order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
    For XML PATH ('Column'), type
)

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC  
For XML PATH ('Table'),Root('Tables')

and this for sps and fns.

 SELECT
      SPECIFIC_SCHEMA     AS '@ObjectSchema'
      ,ROUTINE_NAME       AS '@ObjectName'
      ,ROUTINE_TYPE       AS '@ObjectType'
      ,ROUTINE_DEFINITION AS '@TEXT'
FROM  INFORMATION_SCHEMA.ROUTINES 
WHERE   (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure')
For XML PATH ('Object') , TYPE, ROOT('Objects')

hope that will help you.

Solution 2:[2]

you can use entity data modeling in Visual studio

open created .edmx file with XML text editor, or what ewer you like or need. Good things is that you can generate database model from .edxm file (in Visual Studio).

Solution 3:[3]

Here is a slightly modified version of @MSL's answer that fixes the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. This error is caused when a column in a table has more than one constraint, and was solved by changing the 'Constraint' attribute to be a child node of 'Column' in the XML file.

SELECT TABLE_NAME AS '@Name', CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'Table' ELSE 'View' END AS '@Type',
(
    SELECT Column_Name as '@Name',
        DATA_TYPE as '@DataType',
        case data_type
            when 'nvarchar'
            then CHARACTER_MAXIMUM_LENGTH
            when 'varchar'
            then CHARACTER_MAXIMUM_LENGTH
            else null
        end  as '@Length',
        IS_NULLABLE AS '@IsNullable',
        COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS '@IsIdentity',
        (SELECT tc.CONSTRAINT_TYPE AS '@ConstraintName' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
            WHERE tc.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND cu.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.Column_Name
            For XML PATH ('Constraint'), type )
    FROM INFORMATION_SCHEMA.COLUMNS
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME =
        INFORMATION_SCHEMA.TABLES.TABLE_NAME
    order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
    For XML PATH ('Column'), type
)

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC
For XML PATH ('Table'),Root('Tables')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 nadavesela
Solution 3 Erik McKelvey

Related Questions