Developer42

2015-12-04

SQL Server :: Compare Database Schemas

This is a script to list basic differences in table definitions between two databases.

Background Waffle
When we make database changes in a transactional system, we need to ensure that these won’t adversely affect our BI solution. Normally our developers give us this information when we plan which items to put into a release, so we can liaise with BI; unfortunately the developers machines are down at present due to a disaster at the data center of the company to whom we outsourced development. Since the changes for the release are already in our UAT environment (and only those changes are there), we could determine what would be changing in production by comparing the schemas of the two environments.

My first thought was to use the Database Comparison Tool that comes with Visual Studio. However whilst this does a great job for a developer looking for and resolving differences, it’s not so easy to communicate this information to others (i.e. giving a before and after image of columns side by side. You have to either click on each difference to get a definition of the before and after, then make notes or take screenshots, or you have to generate the DDL, in which case you don’t see the before picture; only the code to amend from the current to the to-be definition.

Instead I realised that I could simply pull the definitions of the tables & columns from SQL, compare these and list the differences; resulting in the code below.
This code can handle where both databases are on the same instance, or where they’re on different instances. For the latter scenario you’d need to create a linked server.
If you frequently compare different combinations of databases and want to avoid amending the instance and catalog names in the code each time you may want to look into views or synonyms (potentially aliases work too if the catalog name is consistent across instances).

Since our BI solution uses replication to get the data from the transactional system, I also included information on which columns were replicated; thus we can filter on those replicated to see just those which BI would be interested in, or can ignore replication to see all differences.

Caveats

  1. We’re not comparing everything; only basic table and column data.
  2. The maxLength value gives length in bytes; not characters (see previous post on how to correct for this).
  3. There are probably other issues; this was a quick fix to resolve an immediate issue which worked for us; there may be issues we’ve not encountered (e.g. maybe the linked server only gives access to a subset of the tables; thus giving false responses)…

Code

set transaction isolation level read uncommitted
go

;with uat as
(
       select t.name TableName
       , c.Name ColumnName
       , ty.name ColType
       , c.max_length ColMaxLength
       , coalesce(c.is_nullable,0) ColIsNullable
       , c.scale ColScale
       , c.[precision] ColPrecision
       , coalesce(c.collation_name,'') ColCollation
       , c.is_replicated
       from [myUatDbServer\uatInstance].[myUatDbCatalog].sys.tables t
       inner join [myUatDbServer\uatInstance].[myUatDbCatalog].sys.columns c
              on c.object_id = t.object_id
       inner join [myUatDbServer\uatInstance].[myUatDbCatalog].sys.types ty
              on ty.system_type_id = c.system_type_id
              and ty.user_type_id = c.user_type_id
)
, prd as
(
       select t.name TableName
       , c.Name ColumnName
       , ty.name ColType
       , c.max_length ColMaxLength
       , coalesce(c.is_nullable,0) ColIsNullable
       , c.scale ColScale
       , c.[precision] ColPrecision
       , coalesce(c.collation_name,'') ColCollation
       , c.is_replicated
       from [myProdDbServer\prodInstance].[myProdDbCatalog].sys.tables t
       inner join [myProdDbServer\prodInstance].[myProdDbCatalog].sys.columns c
              on c.object_id = t.object_id
       inner join [myProdDbServer\prodInstance].[myProdDbCatalog].sys.types ty
              on ty.system_type_id = c.system_type_id
              and ty.user_type_id = c.user_type_id
)
select coalesce(uat.TableName, prd.TableName) TableName
, coalesce(uat.ColumnName, prd.ColumnName) ColumnName
, case
       when prd.TableName is null and not exists (select top 1 1 from prd x where x.TableName = uat.TableName) then 'Add Table'
       when uat.TableName is null and not exists (select top 1 1 from uat x where x.TableName = prd.TableName)  then 'Remove Table'
       when prd.ColumnName is null then 'Add Column'
       when uat.ColumnName is null then 'Remove Column'
       else 'Change Column Definition'
end [AXtion] --our transaction system's Dynamics AX; I'm hilarious :S
, prd.ColType ColTypeFrom
, uat.ColType ColTypeTo
, prd.ColMaxLength ColMaxLengthFrom --not going to fuss about char byte size for now; just want to get a list of changes
, uat.ColMaxLength ColMaxLengthTo --not going to fuss about char byte size for now; just want to get a list of changes
, prd.ColIsNullable ColIsNullableFrom
, uat.ColIsNullable ColIsNullableTo
, prd.ColCollation ColCollationFrom
, uat.ColCollation ColCollationTo
, prd.ColPrecision ColPrecisionFrom
, uat.ColPrecision ColPrecisionTo
, prd.ColScale ColScaleFrom
, uat.ColScale ColScaleTo
, prd.is_replicated PrdIsReplicated --\_these aren't compared; just returned to make it easy to tell what's interesting to BI
, uat.is_replicated UatIsReplicated --/
from uat
full outer join prd
       on prd.TableName = uat.TableName
       and prd.ColumnName = uat.ColumnName
where prd.TableName is null
or uat.TableName is null
or prd.ColumnName is null
or uat.ColumnName is null
or (uat.ColType != prd.ColType)
or (uat.ColMaxLength != prd.ColMaxLength)
or (uat.ColIsNullable != prd.ColIsNullable) 
or (uat.ColCollation != prd.ColCollation) 
or (uat.ColPrecision != prd.ColPrecision) 
or (uat.ColScale != prd.ColScale)
order by coalesce(uat.TableName, prd.TableName) 
, coalesce(uat.ColumnName, prd.ColumnName) 
Advertisements

2010-06-29

My First Python Plugin

Filed under: Technology, WordPress — Tags: , , , , , , , , — Developer42 @ 22:51

I’ve read a lot about Python in the past, but have been so inundated with things to learn recently that it had always fallen to the bottom of the pile. However, recently I requested a feature for my favourite text editor, Programmers’ Notepad, and heard back from its developer, Simon, within moments of submitting my request. He’d provided a solution and instructions on how to implement it, which introduced me to a powerful set of features I’d previously been unaware of (namely that you could run apps which output to the command line straight from the tools menu, and have their output returned into the current document). After realising I’d been missing out on some of this potential, I spent a bit of time going over the pnotepad.org site, seeing what else I’d missed. This is where I come back to Python. PNotepad allows you to write custom scripts in Python, put them into pn’s \scripts directory, and run them from the scripts toolbar the next time you start up the editor. I figured any time spent learning python would easily be offset by the hours saved by having scripts take care of laborious tasks for me, so promptly downloaded and installed Python and PyPN (which enables Python scripts in PN), had a look at a script from the script repository, and a browse through the python manual, and then wrote my first script in a few minutes. For anyone wanting to see it, my script is a simple tool for replacing < > and & characters with their HTML codes, and inserting the PRE and CODE tags required to publish code to wordpress. I’ve uploaded it to the script share, here.

First Impressions
My first impressions of Python, after having coded for all of 20 mins or so, are that it’s a really simple language to learn, which anyone (including non developers) should be able to pick up pretty quickly. There are a few bits I’m not so keen on; select statements don’t exist, using instead piles of else-if (elif) statements, and not having curly brackets and semi-colons, but instead relying on indentation leaves me feeling a little out of my comfort zone, but all this is just a familiarity thing, which I’m sure I’ll soon get over. I’m hoping to write a few more scripts over the next few weeks (assuming I can find interesting and required things to write), and to learn a bit more about Python outside of PN, after which I’ll report back on my experiences for anyone else interested in heading in that direction.

Steps Taken to Run Python in PN
If you’d also like to get into Python for PN, here’s a step by step set of instructions on what to do (skipping a few steps to avoid typing “click next” too many times).
Download & install/extract the following, in the order listed below:

Once installed, go to the command line (windows key + r, cmd, enter), navigate to the programmer’s notepad directory (cd %programfiles%\programmer*), then type the following “pn –findexts”. It looks like nothing’s happened, but that’s good; it hasn’t errorred.
Next, load up programmer’s notepad, click view, windows, scripts (or press alt+F10), and you should see a list of available scripts.
Get an existing script from the script share site: http://scriptshare.rocketmonkeys.com/ and save the file to your …\Programmer’s Notepad\scripts\ directory.
Close and reopen PN. You should now see a new script (or several) listed in the scripts toolbar.
From here on, it’s pretty easy to figure out what to do to create your own scripts by looking at existing scripts and python code, and playing.
Good luck.

Blog at WordPress.com.

%d bloggers like this: