Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Mattsql