Cross Server Dictionary

• Sim-473257

  Mr or Mrs. 500

  Points: 532

  More actions

  April 15, 2013 at 5:55 am

  #271061

  I guess this post is not a 'question' per se, but more just me rambling on about my approach so far, and see what feedback comes in return.

  Maybe I've overlooked some points, maybe (likely) there's a better solution/approach out there....?

  I'm just seeking the opportunity to bounce my world against people and see what they think.

  Background

  So, I am tasked to document our databases.

  The idea being that whether you are a developer, reporter or occasional user.... you are not just victim to interpreting an ambiguous table/field name, but can access a wide range of support information.

  The new auxiliary tables would in simplest terms contain textual descriptions, links to supporting documentation outside of database.

  In addition, there would be a hierarchy to group field of equal and similar significance (departure location, arrival location, manufacturing location .. would all roll up to an entity called 'location')

  Phase 1 was just to prototype a dictionary with 1 database, but now Phase 2 is to create 1 single dictionary to contain the meta-data of multiple databases residing on different servers.

  In an ideal world, I'd like to have the best use of existing system tables as possible for the foundation level, after all, why duplicate something that already exists and if maintained for you automatically ?

  However, I have struggled with some points of merging and utilizing system data form multiple databases/servers... and I am beginning to re-invent a new system table structure.

  Thus I'm baring my progress so far in the hope that someone can help re focus how I can make all of this more streamline !

  Approach (so far!)

  As we know, we can find all sorts of information about tables, fields, relationships from various system tables and information_schema views.

  Yet my first challenge was how to link the SQL Server system data, to my auxiliary data.

  Linking full textual tablename/fieldname would be just inefficient; ID fields would be needed.

  In an ideal world, I could just use sys.objects object_id, with an additional field to differentiate severs.

  However, just utilizing the system object_id's might cause problems down the line, as these may change in the case of objects being modified/dropped/recreated. This would cause my subsequent hierarchies to fall apart.

  In the end I decided that I would require both a permanent non-system ID that would identify a unique field/table for my aux data, and also a procedure to assign to that row, the current system object_id. (weekly job to synchronize based on catalog/schema/table/field name).

  Hence I will maintain a permanent auxiliary structure with my own Identities, and be able to reference system tables with the normal object_id.

  My new table now looks something like this :

  

  For Phase 2, we have to accommodate objects from multiple databases.

  

  So that's where I am at right now.

  Feel free to rip it all apart and show me a simpler way to create a dictionary that will bridge databases and servers !