June 13, 2016 at 5:31 am
I have One Metadata table, which has got all tablenames, column names and their datatypes. we are created relative tables using this metadata table, but I have got requrirement to create a sp, if there is any column added to thie metadata table, I need to add this column to actual table by passing tablename as parameter for SP. Any ideas would be great.
Thank you
June 13, 2016 at 7:36 am
This is a scary idea. I'm not sure that letting people modify the schema.
That being said, it could be done by using a trigger and dynamic SQL.
As I'm against this idea, I'm not giving much details. I could end up blamed for this. :hehe:
June 13, 2016 at 8:24 am
There are systems that do this, like Sharepoint and some vendor applications. Almost completely they usually perform poorly and don't run well. Data gets duplicated or lost because there isn't any thought put into design, indexing, etc.
If this is for limited use, then you can use dynamic SQL to do this. You'd be using sp_executesql or exec() to run code, and you'd have to write your own code that essentially builds the proper "ALTER TABLE xxx ADD yyy" statement.
However, you need to do this in an idempotent way. You don't want to run
ALTER TABLE mytable add mycol int
and there already be a mycol column. So you need error checking in your code.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 13, 2016 at 8:48 am
Steve Jones - SSC Editor (6/13/2016)
However, you need to do this in an idempotent way. You don't want to runALTER TABLE mytable add mycol int
and there already be a mycol column. So you need error checking in your code.
Isn't that the reason to have unique constraints?
June 13, 2016 at 12:16 pm
Maybe. What if someone adds it and deletes it and adds it back?
I can see users doing this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply