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?
- Use scripts only to make any sort of changes
- Save all scripts in a folder per project or feature you are working on for example
183737_20190128 - Under this folder have all of your
Schema, view, procs and functions in separate folders - 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?