Amending Table Structure Using Metadata Table

Transact-SQL
1

HI

Was wondering if anyone has come across this issue or could help solve it.

Currently we have a table which holds contact information

Data Table:

ContactID, FirstTransactionDate, FirstTransactionValue

1,2016-03-01,10.00

2,2016-03-01,25.00

…

We are looking into a method of holding the table’s metadata in a separate table

Metadata Table:

ColumnName,DataType,Length

ContactID,INT,,

FirstTransactionDate,DATETIME,,

FirstTransactionValue,DECIMAL,10

So the process we need to do is check the Metadata Table and if there is a column name which exists in there, but not in the Data Table, then it needs to be created.

So in theory if the Metadata Table was populated with the following:

Metadata Table:

ColumnName,DataType,Length

ContactID,INT,,

FirstTransactionDate,DATETIME,,

FirstTransactionValue,DECIMAL,10

LastTransactionDate,DATETIME,,

LastTransactionValue,DECIMAL,10

Then the process would identify that the last two columns don’t exist in the Data Table and create them accordingly.

So the data table structure would be:

ContactID, FirstTransactionDate, FirstTransactionValue, LastTransactionDate, LastTransactionValue

Has anyone come across this issue before and know a method of doing this?

Thanks in advance!

2

Yes possible. But you will required more information on your metadata table. Like table name, data type precision, scale etc

here is an example based on the information you provided

declare    @sql    nvarchar(max)

select    @sql    = N'ALTER TABLE ' + QUOTENAME('Data_Table') + ' '
        + N'ADD ' + QUOTENAME(ColumnName) + ' ' 
        +  DataType + ' ' + case DataType  when 'DECIMAL' then '(' + convert(varchar(10), Length) + ')' else '' end
from    Metadata m
where    not exists 
    (
        select    *
        from    sys.columns x
        where    x.object_id    = object_id('Data_Table')
        and    x.name        = m.ColumnName
    )

print    @sql
exec    sp_executesql @sql
3

Yes, we do that. We hold "rich" Metadata about the database - e.g. including things like a nice friendly description for each column in every language that the APP supports so that Forms can display that, and if it changes we only need to change it in the one, central, location.

One problem we have is if someone created / modified a table OUTSIDE our Meta Data. We have huge scripts that attempt to compare the physical database with the Meta data and figure out what to change. Its complicated if a column is renamed - and thus becomes out of SYNC. Its kinda-OK if the column stays in the same place (i.e. the Column Order ID does not change), but when a column is "moved" it gets messy.

We have a "Most recently applied" copy of the Meta Data, in a separate (parallel) table so we can compare OUR New Meta Data with OUR Original Meta Data, which makes it much easier to generate "change" scripts. We then just check that the Original meta Data table does actually match the physical structure of the DB (i.e. there have been no external changes) before generating a Change Script.

If we want to update a client's database we don't need to apply all the Change Scripts, in order, and then find that there was something Goofy on their particular database; we can just freshen up the Meta Data tables, their Original Meta Data tables should match their physical DB, and then we just generate a script which will modify their database to match the current Meta Data.

There's a fair bit more to it than that ... evolved over the last couple of decades! but, for us, we think it improves our productivity over having the "basic" Meta Data in the DDL of the physical DB and then a whole load more, richer, Meta Data buried within the APP (e.g. Forms and their Legends, or Reports and the names of Column Headings etc.) and also lets us have lots of mechanically generated code (much cheaper, and more reliable! than anything the Humans here write!) and also checks within the system that everything that should be there IS - no missing Indexes, Triggers, Constrains and so on ...

4

Thanks Khtan, will give this a try.

5

This is off topic, but I thought it might be useful to mention in this context of Meta Data

The thread reminded me of an interesting SQL Lunch Video by Cade Roux entitled "Get a Lever and Pick Any Turtle: Lifting with Metadata". He talks about using the standard MS Properties (available on all objects such as Tables, Views, Column, Indexes etc.) to store his own Meta Data. I;ve only ever used them for Descriptions of objects, as I felt that the Key/Value system was way too imprecise ... but Cade takes exactly the opposite view and turns that to his advantage.

First of all store, on a table, a property that the table is part of "Health Check Management". Then store attributes against the table / columns for various aspects - for example the Tables under Health Check Management can be categorised into Sub-systems. Examples Cade gives include:

"Add a TODO property"

“I want to see NULLability (base metadata) of all money (base metadata) columns in tables in the Accounts subsystem (extended property) which have not been marked as reviewed. (extended property)”

He then has a property to allow a value of EXCLUDE against a property. For example a Table Design Rule "No varchar(N) columns where N <= 2" can have a SQL Query which lists columns that violate this rule, but only for tables where the "Health Check" property is TRUE and omitting any columns that have the EXCLUDE value set for this property.

Cade provides VIEWs and SProcs to manage the data (in a much more uniform style than the MS provided SProcs - e.g. a Create SProc that doesn't require choice of ADD or UPDATE variants - which has always cheesed me off!!)

He then creates a series of SProcs to check ThisRule and ThatRule, sets an Attribute on each [Sproc] for which Health Check SET it belongs to, and then there is a Master SProc that will run all the SProcs within a specific Health Check SET (or all in any such SET). You wind up with multiple resulsets showing all Rule Violations - or, hopefully, no output at all!

http://sqllunch.com/Meeting.aspx?lunchid=41

There is a video, but I only found it as a download file - YouTube would suit me much better ...
http://www.sqllunch.com/videos/lunch41.wmv (in particular as I like to watch such things at at least 1.5x as there is always a lot of slow-speech in documentary type videos and time is precious ...)

I have my Meta Data in specific tables. Cade suggests that that requires deployment of all the tables etc. and then updates to those tables, in all databases, as their features "grow", whereas the Properties Key/Value table within SQL Server doesn't have that restriction. My concern would be that the Values in the Properties table are unvalidated (its a SQLVariant datatype, so they could be any anything ...) but Cade gets around that by using all-distinct-values of a Property Key to display in his little Front End Utility, so misspellings etc. would get weeded out at some point. His approach is elegantly lightweight

6

Thanks worked great.

Added the additional information and put it into a WHILE loop to go through all the columns.

Thanks again.