Compare two database schema in two different server
This article explains about comparison between two database schema in two different server. While updates in live server, we are able to get all the scripts of stored procedures and execute directly to the server. At the same it will not possible to execute the table scripts because it may have lot of real time data. So confusions occur while updating our table structure changes to live server. I have found a good and easiest solution to upgrade our table structures to live server.
Introduction:
This article explains about comparison between two database schema in two different server. While developing a web or windows based application, we need to change the table structures and stored procedures frequently in local database. After the completion of the changes, we need to update all the changes to live server. During updation in live server , we are able to get all the scripts of stored procedures and execute directly to the server. At the same it will not possible to execute the table scripts because it may have lot of real time data. So confusions occur while updating our table structure changes to live server. So there is need to maintain a document or sql query about the table structure changes. I have faced the same difficulties during my development and got lot of issues on that. I have found a good and easiest solution to upgrade our table structures to live server.
Steps:
1) First, we need to get all the table structure as an XML data from both the database (Local and server). So that, we can easily compare the fields and identify the missing fields.
2) Using the below SQL query, we can easily get the list of tables and fields with their data types and size,
SELECT TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION FROM Employee.INFORMATION_SCHEMA.COLUMNS
(Employee – Name of the database)
Once you execute the above query then the result will be displayed as like the below screen,
3) The above code returns the list of table with their properties. But, we can't execute this query for the two servers. So, we need to convert that result as XML.
4) Using the below query, we can convert the result as a XML.
SELECT TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION FROM Employee.INFORMATION_SCHEMA.COLUMNS
FOR XML RAW('TABLE'),ELEMENTS,ROOT('DATA')
5) First, we need to collect this XML from both the database (Which we need to compare)
6) After collecting the XML data, using the below query we can identify the missed tables, fields or their data type.
DECLARE @DATA XML
SET @DATA = 'PASTE YOUR XML DATA OF DATABASE 1'
DECLARE @DATA1 XML
SET @DATA1 ='PASTE YOUR XML DATA OF DATABASE 2'
SELECT *
FROM
(
SELECT xmlobj.value('TABLE_NAME[1]','VARCHAR(500)') AS TABLE_NAME,
xmlobj.value('COLUMN_NAME[1]','VARCHAR(500)') AS COLUMN_NAME,
xmlobj.value('IS_NULLABLE[1]','VARCHAR(500)') AS IS_NULLABLE,
xmlobj.value('DATA_TYPE[1]','VARCHAR(500)') AS DATA_TYPE,
xmlobj.value('CHARACTER_MAXIMUM_LENGTH[1]','VARCHAR(500)') AS CHARACTER_MAXIMUM_LENGTH
FROM @DATA.nodes('//TABLE') AS R (XMLOBJ)
EXCEPT
SELECT xmlobj.value('TABLE_NAME[1]','VARCHAR(500)') AS TABLE_NAME,
xmlobj.value('COLUMN_NAME[1]','VARCHAR(500)') AS COLUMN_NAME,
xmlobj.value('IS_NULLABLE[1]','VARCHAR(500)') AS IS_NULLABLE,
xmlobj.value('DATA_TYPE[1]','VARCHAR(500)') AS DATA_TYPE,
xmlobj.value('CHARACTER_MAXIMUM_LENGTH[1]','VARCHAR(500)') AS CHARACTER_MAXIMUM_LENGTH
FROM @DATA1.nodes('//TABLE') AS R (XMLOBJ)
)T
7) The above mentioned query lists the table which is not available in any one of the database.
A complete sample scenario:
I have the database as follows, Employee_Local and Employee_Live. Now, I have newly added one tables and modified the data type of the existing table.
Schema of Employee_Local as XML:
In this database, I have newly added "tblUserRole" table and modified the size of "FullName". Also, added two fields "ResetPasswordKey & IsPasswordReset" field to "tblUserAccount" table. Please refer the "Employee_Local_XML" attachment.
Schema of Employee_Live as XML:
In this database, it has only one table named "tblUserAccount". So, now I need to compare this database schema with my local database schema.
Please refer the "Employee_Live_XML" attachment.
For your reference, Herewith, I attached the complete SQL query.
After executes the above query, it gives the result as like the attached screen shot.
As per the above instruction, it shows the list of fields had been modified and newly created tables. This result determines updates and changes updated to live database.
Drawbacks:
The main drawback is we cannot differentiate the newly modified Field from newly created field.
Future Implementation:
Now, I am focusing about bringing back the missed tables, fields and the modification using a complete dynamic query which would be automated.
Attachments
