Export database schema into XML file

Question

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

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

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

You can script all these things out and then convert them to xml? What have you tried so far? — NickyvV
– NickyvV, Commented Aug 11, 2016 at 8:14
I am not so well in SQL. So far I found how can I do it with tables, but I also need views, functions and SPs. — XWiśniowiecki
– XWiśniowiecki, Commented Aug 11, 2016 at 8:16

MSL · Accepted Answer · 2016-08-11 09:04:59Z

3

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.

edited Aug 11, 2016 at 9:04

answered Aug 11, 2016 at 8:36

MSL

1,0101 gold badge13 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sathish Over a year ago

For 1st query I am getting the following error, Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How to solve this?

nadavesela · Accepted Answer · 2018-04-27 07:37:53Z

1

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).

answered Apr 27, 2018 at 7:37

nadavesela

111 bronze badge

Comments

Erik McKelvey · Accepted Answer · 2022-05-02 00:21:54Z

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')

Collectives™ on Stack Overflow

Export database schema into XML file

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related