Database comparison 2

[SiJP]

Before I go on a major coding mission, I' need a bit of advice regarding database comparisons.

I have one SQL 2000 server that contains two database's:

CLIENTINFO
CLIENTINFOnew

The second db contains new tables and new fields.

Now, I'm planing on using ADO to loop through the sysobjects table to ascertain which new tables need adding (then add them). I know I can do this, as I have tested this method.

What I don't know how to do, is the same for the fields of each table e.g. check old database's 'address' table against the new database's 'address' table, establish that the new field 'postcode' needs adding, then add it.

So, a couple of Q's...

1) am i going about the whole database comparison in a rather gruesome way? i.e. is there an easier way?

2) If I am going about it the right way, where can i get column info for a table?

Ultimately, I'm trying to create an easy way to supply db structure updates to clients, whilst retaining existing data, without me scripting the changes! (I'm a lazy devil!)

Thanks,

------------------------
Hit any User to continue

 

[vongrunt]

> 1) am i going about the whole database comparison in a rather gruesome way?

It depends. Completely accurate comparison is never possible. For example, if you renamed table or column in new version then software can only guess for matches. Plus with increased dependency of database objects(indexes, foreign keys, stats, procs/functions, blah) complexity of comparison code goes waaaaay up.

> i.e. is there an easier way?

Yup, buy 3rdparty software for database comparison (go to

http://www.db-review.com,

google for "red gate" etc).

2) If I am going about it the right way, where can i get column info for a table?

Many ways:

- manual queries over sys* tables
- stored procedures a la sp_columns, sp_tables, sp_pkeys etc.
- INFORMATION_SCHEMA views
- ADO .OpenSchema method
- ADOX.Catalog

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Catadmin]

Aha! A code question I can actually answer. @=) I created a data dictionary via SQL Reporting Services to get a list of all tables & columns and extended properties in a database. You might consider the same.

In the meantime, though, maybe the following stored procedures will help...

Table List:

REATE PROCEDURE [dbo].[ddsp_ListTableNames] 

AS
--  ----------------------------------------------------------------------------------------------------------------
--
--	Purpose: List Table Names from system table to populate drop down list.
--	Created: 3/10/05 bmt
--  ----------------------------------------------------------------------------------------------------------------

 Select [name] as TableName
 From sysobjects 
 Where xtype = 'U'
 Order by [name] asc

GO

Column Details

CREATE Procedure ddsp_AllColumnDetails1 @tblName varchar(255)
AS
            Select distinct
		o.[name] as TableName,
	 	c.[name] as ColumnName,
		c.colorder as ColumnNum,
	 	Case
                    When e.[value] is null or e.[value] = ' ' 
                     Then 'Column Descrip N/A'
	 	    Else e.[value] 
                End as ColumnDefinition,
	 	t.[name] as DataType,
	 	c.length as Length,
	 	c.xprec  as [Precision], 
	 	c.xscale as Scale,
	 	Case
	    	     When c.isnullable = 0 Then 'No'
	    	     When c.isnullable = 1 Then 'Yes'
	 	End as AllowsNull,
		Case
		     When c.cdefault = null or c.cdefault = 0 Then 'NONE'
	 	     When c.cdefault > 0 Then (Select distinct m.[text] from sysobjects o with (nolock), syscolumns c with (nolock), syscomments m with (nolock) 
					 	where o.[id] = c.[id] and  c.cdefault = m.[id] and o.[name] = @tblName )
		End as ColumnDefault
  	From sysobjects o with (nolock) inner join syscolumns c with (nolock) on o.[id] = c.[id]
	  		left join ::fn_listextendedproperty('MS_Description','user','dbo','table', @tblName, 'column', default) e 
			on c.[name] = e.objname, systypes t with (nolock), syscomments m with (nolock)
  	Where o.xtype = 'U' and
	 		c.xtype = t.xtype and
			o.[name] = @tblName 
	order by o.[name], c.colorder
GO

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SiJP]

Hi guys, thanks both the replies.

Vongrunt, I had a feeling that adox was cropping into things, and may well shoot down this route!

Catadmin.. if the above fails, I may well use your little script!



------------------------
Hit any User to continue

 

[ESquared]

I did this as follows:

SELECT L.* INTO #MissingColumns FROM
  OPENQUERY (RemoteDatabase, 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS') L
  LEFT JOIN LocalDatabase.INFORMATION_SCHEMA.Columns B ON L.TABLE_NAME = B.TABLE_NAME AND L.COLUMN_NAME = B.COLUMN_NAME
  WHERE B.TABLE_NAME IS NULL
UNION SELECT B.* FROM
  OPENQUERY (RemoteDatabase, 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS') L
  RIGHT JOIN LocalDatabase.INFORMATION_SCHEMA.Columns B ON L.TABLE_NAME = B.TABLE_NAME AND L.COLUMN_NAME = B.COLUMN_NAME
  WHERE L.TABLE_NAME IS NULL

IF (SELECT Count(1) FROM #MissingColumns) > 0 BEGIN
  PRINT 'Warning: schema mismatch between databases:'
  SELECT * FROM #MissingColumns ORDER BY TABLE_NAME, COLUMN_NAME, TABLE_CATALOG
END

DROP TABLE #MissingColumns

This just shows a list of columns that are missing in the other compared database (one was a linked server for me). It doesn't compare data types or anything else, but it should be easy to add that, just add more conditions to the join clause, such as AND DATA_TYPE = DATA_TYPE... watch out for nullable columns.

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E