How to export DB schema to xml?

I need to export DB schema as xml for tables, index, functions and procedures. For that I need any open source tool or library or sql query. Which could perform this operation?

What xml format you want it to look iike

```
create table sample(id numeric(5) primary key)

<Tables>
  <Table Name="sample" Type="Table">
    <Column Name="id" DataType="numeric" IsNullable="NO" IsIdentity="0" Constraint="PRIMARY KEY" />
  </Table>
</Tables>

I need something like this, samething for index, functions & procedures

So why do you need it in xml format and not in native sql file format

I am writing the powershell script to copy the DB the schema to compare two system for checking the changes. Based on the changes I can make the alter commands

Is this a one way change or 2 way change?
How are changes being made in db1? Manually or through a script?

It can be in 2 way?

No I am asking you, do you keep track of changes in both databases and update the other

Yes, I will make the changes accordingly. If they face any problem. My need is to identify the changes between two DB.

Example: In system 1 there is a student tables like this

create table student(id numeric(10) name varchar(25))

In system 2

create table student(id numeric(10) name varchar(20))

In system 1 the name length is 25 where as in system 2 it is 20. So I will identify it by comparing the. And I will make the alter command based on that

How are schema, proc,views changes currently being made manually or via scripts?

Through scripts as well as manually that can be in two way

Who is making these changes to either db1 or db2. This approach sounds fragile imo and tough to manage. Would you be open to a different recommendation

Is there any different recommendation?

  1. Use scripts only to make any sort of changes
  2. Save all scripts in a folder per project or feature you are working on for example
    183737_20190128
  3. Under this folder have all of your
    Schema, view, procs and functions in separate folders
  4. Use powershell or tools like red gate to deploy to all of your databases at the same time hence keeping them in synch always

Try this in dev servers then a test then production

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

I have used this query but it is showing the following error. It generates the xml when I executed the DB with only tables. But I don't know the problem. I'm getting below 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?