Baya Pavliashvili
03 Apr 2002, Rating 4.25 (out of 5)
If you work for a small company, then your development,
test and production databases might reside on the same server.
However, most organizations realize the risks associated with
such a setup. If your server were to become unusable due to a
disk failure or power outage, you'd lose not only the business
from your current customers, but also the valuable development
time. If you use a production database for development and
developers make a mistake, your online customers would have to
wait until your production database was restored from a
backup. The bottom line is that no serious development should
be done on the production server. The application should be
developed on one server, tested in a production-like
environment on another server (sometimes referred to as
Quality Assurance server), and then deployed to the production
server.
The initial migration of the database is easy; you can just
backup the database on the development server and restore it
on the production server. But what happens if you have
subsequent releases or enhancements to the already deployed
application? Well, backup and restore is no longer an
option--you'd lose all the data collected in production. DTS
could be helpful only if your database structure is the same
on all servers. In the majority of cases your database
structure will change from one release to the next; therefore,
you need some type of a mechanism to compare databases on two
servers and come up with a plan to synchronize them. In this
article, I offer you a script that compares tables, indexes
and constraints on two servers and generates a comprehensive
report of differences.
As you might guess, the trick is in examining the system
tables and/or the information schema views. The script itself
isn't terribly complicated, but if your situation calls for
comparing database structures, it will give you a good head
start for developing your own solution. Once you know the
differences between the database structures, you can come up
with scripts to make appropriate changes to the production
database and transfer/transform additional data from the
development environment.
The following script compares development and production
databases on my test server. Replace the database names if you
have to compare two databases on the same server. If you have
separate servers, you'll have to set up the remote server as a
linked server and modify this script to query the remote
database. See inline comments for details.
SET NOCOUNT ON
DECLARE @table_name VARCHAR(100),
@index_name VARCHAR(100),
@sql VARCHAR(4000)
-- temp tables to keep intermediate values:
CREATE TABLE #dev_columns (
table_name VARCHAR(100) NULL,
column_id INT NULL,
column_name VARCHAR(100) NULL,
data_type VARCHAR(100) NULL,
length INT,
nullable BIT)
CREATE TABLE #production_columns (
table_name VARCHAR(100) NULL,
column_id INT NULL,
column_name VARCHAR(100) NULL,
data_type VARCHAR(100) NULL,
length INT,
nullable BIT)
CREATE TABLE #dev_indexes (
table_name VARCHAR(100),
index_name VARCHAR(100),
column_name VARCHAR(100),
ordinal_position_of_column INT,
isclustered BIT)
CREATE TABLE #production_indexes (
table_name VARCHAR(100),
index_name VARCHAR(100),
column_name VARCHAR(100),
ordinal_position_of_column INT,
isclustered BIT)
CREATE TABLE #dev_constraints (
table_name VARCHAR(100),
constraint_type VARCHAR(25),
constraint_name VARCHAR(100),
column_name VARCHAR(100),
constraint_value VARCHAR(255))
CREATE TABLE #production_constraints (
table_name VARCHAR(100),
constraint_type VARCHAR(25),
constraint_name VARCHAR(100),
column_name VARCHAR(100),
constraint_value VARCHAR(255))
-- get all columns, indexes, and constraints from first db:
USE Development
DECLARE table_cursor CURSOR FOR
SELECT name
FROM Development.dbo.sysobjects
WHERE type = 'u'
AND name <> 'dtproperties'
ORDER BY 1
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'INSERT #dev_columns (
table_name ,
column_id ,
column_name ,
data_type ,
length ,
nullable )
SELECT ''' + @table_name + ''' ,
colid,
a.name,
b.name,
a.length,
a.isnullable
FROM syscolumns a
INNER JOIN systypes b
ON a.xtype = b.xtype
and a.xusertype = b.xusertype
WHERE
a.id = OBJECT_ID(''' + @table_name + ''')'
EXEC(@sql)
SELECT @sql = ' '
SELECT @sql = 'INSERT #dev_indexes
SELECT DISTINCT b.name AS table_name,
a.name AS index_name,
d.name AS column_name,
c.keyno AS ordinal_position_of_column,
''CLUSTERED'' = CASE
WHEN a.indid = 1 THEN 1
ELSE 0
END
FROM sysindexes a INNER JOIN sysobjects b
ON a.id = b.id
AND b.type = ''u''
and b.name <> ''dtproperties''
INNER JOIN sysindexkeys c
ON a.indid = c.indid AND c.id = b.id
INNER JOIN syscolumns d
ON d.colid = c.colid
AND d.id = b.id
WHERE a.indid <> 0
AND a.indid <> 255
AND a.name NOT LIKE ''_wa%''
AND b.name = ''' + @table_name + '''
ORDER BY 1, 2, 4'
EXEC(@sql)
SELECT @sql = ' '
SELECT @sql = 'INSERT #dev_constraints
SELECT b.name AS table_name,
constraint_type =
CASE
WHEN a.type = ''c''
THEN ''CHECK''
WHEN a.type = ''d''
THEN ''DEFAULT''
END,
a.name AS constraint_name,
COL_NAME(b.id, a.info)
AS column_name,
c.text AS constraint_value
FROM sysobjects a
INNER JOIN sysobjects b
ON b.id = a.parent_obj
LEFT JOIN syscomments c
ON c.id = a.id
WHERE a.type IN (''c'', ''d'')
AND b.name = ''' + @table_name +''''
EXEC(@sql)
FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
-- get all columns, indexes, and constraints from second db:
USE Production
/*******************************************
* if using a remote server then remove
* "USE production" statement and use
* four-part identifier for querying
* the tables, as in following
*
* SELECT name
* FROM
* linked_server.remote_database.dbo.sysobjects
****************************************
*/
DECLARE table_cursor CURSOR FOR
SELECT name FROM Production.dbo.sysobjects
WHERE type = 'u'
AND name <> 'dtproperties'
ORDER BY 1
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'INSERT #production_columns (
table_name ,
column_id ,
column_name ,
data_type ,
length ,
nullable )
SELECT ''' + @table_name + ''' ,
colid,
a.name,
b.name,
a.length,
a.isnullable
FROM syscolumns a INNER JOIN systypes b
ON a.xtype = b.xtype
and a.xusertype = b.xusertype
WHERE a.id = OBJECT_ID(''' + @table_name + ''')'
EXEC(@sql)
SELECT @sql = ' '
SELECT @sql = 'INSERT #production_indexes
SELECT
DISTINCT
b.name AS table_name,
a.name AS index_name,
d.name AS column_name,
c.keyno AS ordinal_position_of_column,
'CLUSTERED' =
CASE
WHEN a.indid = 1 THEN 1
ELSE 0
END
FROM sysindexes a
INNER JOIN sysobjects b
ON a.id = b.id
AND b.type = ''u''
INNER JOIN sysindexkeys c
ON a.indid = c.indid AND c.id = b.id
INNER JOIN syscolumns d
ON d.colid = c.colid AND d.id = b.id
WHERE a.indid <> 0
AND a.indid <> 255
AND a.name NOT LIKE ''_wa%''
AND b.name = ''' + @table_name + '''
ORDER BY 1, 2, 4'
EXEC(@sql)
SELECT @sql = ' '
SELECT @sql = 'INSERT #production_constraints
SELECT b.name AS table_name,
constraint_type =
CASE
WHEN a.type = ''c''
THEN ''CHECK''
WHEN a.type = ''d''
THEN ''DEFAULT''
END,
a.name AS constraint_name,
COL_NAME(b.id, a.info)
AS column_name,
c.text AS constraint_value
FROM sysobjects a INNER JOIN sysobjects b
ON b.id = a.parent_obj
LEFT JOIN syscomments c ON c.id = a.id
WHERE a.type IN (''c'', ''d'')
AND
b.name = ''' + @table_name +''''
EXEC(@sql)
FETCH NEXT FROM table_cursor INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
-- now compare the values:
SELECT 'the following tables are in development, but not in production database:'
SELECT DISTINCT table_name FROM #dev_columns
WHERE table_name NOT IN
(SELECT table_name FROM #production_columns)
SELECT 'the following tables are in production, but not in development database:'
SELECT DISTINCT table_name
FROM #production_columns
WHERE table_name
NOT IN (SELECT table_name FROM #dev_columns)
SELECT 'the following columns are in development, but not in production database:'
SELECT a.* FROM #dev_columns a
WHERE a.column_name
NOT IN (SELECT column_name
FROM #production_columns b
WHERE a.table_name = b.table_name)
SELECT 'the following columns are in production, but not in development database:'
SELECT a.* FROM #production_columns a
WHERE a.column_name NOT IN
(SELECT column_name FROM #dev_columns b
WHERE a.table_name = b.table_name)
SELECT 'the following columns have varying data types, length or nullability:'
SELECT
DISTINCT
a.table_name,
a.column_name,
a.data_type AS dev_dt,
a.length AS dev_length,
a.nullable AS dev_nullability,
b.data_type AS production_dt,
b.length AS production_length,
b.nullable AS production_nullability
FROM #dev_columns a
INNER JOIN #production_columns b ON
a.column_name = b.column_name
AND
a.table_name = b.table_name
AND
(a.data_type <> b.data_type
OR
a.length <> b.length
OR
a.nullable <> b.nullable)
ORDER BY 1
DROP TABLE #dev_columns, #production_columns
SELECT 'the following tables have indexes in dev, but not
production:'
SELECT DISTINCT table_name
FROM #dev_indexes
WHERE table_name
NOT IN (SELECT table_name
FROM #production_indexes)
SELECT 'the following tables have indexes in production, but
not dev:'
SELECT DISTINCT table_name
FROM #production_indexes
WHERE table_name
NOT IN (SELECT table_name FROM #dev_indexes)
SELECT 'the following indexes appear in dev, but not in
production:'
SELECT * FROM #dev_indexes a
WHERE index_name NOT IN
(SELECT index_name
FROM #production_indexes b
WHERE a.table_name = b.table_name)
SELECT 'the following indexes appear in production, but not in development:'
SELECT * FROM #production_indexes a
WHERE index_name NOT IN
(SELECT index_name FROM #dev_indexes b
WHERE a.table_name =
b.table_name)
SELECT 'the ordinal position of columns is different in
following indexes:'
SELECT
a.table_name,
a.index_name,
a.ordinal_position_of_column as 'dev_position',
b.ordinal_position_of_column as 'production_position'
FROM #dev_indexes a
INNER JOIN #production_indexes b ON
a.table_name = b.table_name
AND
a.index_name = b.index_name
AND
a.column_name = b.column_name
AND
a.ordinal_position_of_column <>
b.ordinal_position_of_column
SELECT 'the following indexes are clustered in development
but not in production, or vice versa:'
SELECT
a.table_name,
a.index_name,
a.isclustered AS isclustered_in_dev,
b.isclustered AS isclustered_in_production
FROM #dev_indexes a
INNER JOIN #production_indexes b ON
a.table_name = b.table_name
AND
a.index_name = b.index_name
AND
a.isclustered <> b.isclustered
DROP TABLE #dev_indexes, #production_indexes
SELECT 'the following constraints appear in development,
but not in production:'
SELECT * FROM #dev_constraints WHERE constraint_name
NOT IN
(SELECT DISTINCT constraint_name
FROM #production_constraints)
SELECT 'the following constraints appear in production but not in development:'
SELECT * FROM #production_constraints
WHERE constraint_name NOT IN
(SELECT DISTINCT constraint_name
FROM #dev_constraints)
DROP TABLE #dev_constraints, #production_constraints
Reader Feedback
Mike C. writes: I have found that calling the
OBJECT_ID function across linked servers does not work however
you attempt to prefix it. But you can amend the two lines of
the format: WHERE a.id = OBJECT_ID(''' + @table_name + ''')'
...to be like: WHERE a.id = (
SELECT id
FROM server2.db_name.dbo.sysobjects
WHERE name = ''' + @table_name + '''
)'
|
