Create objects under multiple schemas
-
Hello All,
Is there a way by which if I create an object under the default dbo schema the object should then get duplicated under rest of the existing schemas in the database?
Example : If I create a table T under dbo schema ,the same table T should then be created under all the schemas that exist in the database.
-
If you grant users execute rights on the schema, then they can access the objects inside it. What are you trying to accomplish by doing this?
-
Suppose I have schema1 and schema2 in my database. So when I create a table say T under dbo schema,schema1.T and schema2.T should get created. It does not have to instantaneous.
-
Have you looked at synonyms for this?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Unfortunately synonyms won't work in my case..
-
Do you want the same object visible in each schema? Meaning I create foo.TableA and bar.TableA and it's the same table with the same data? If so, no. An object cannot exist in multiple schemas. A synonym would be your only choice here, and not sure that does what you want.
If you want objects duplicated as separate entities in another schema with the same structure, you could use a trigger that recreates an object in mutliple schemas, but really, I'd have the user be responsible for creating the object in multiple schemas.
What exactly are you trying to accomplish here or what is the use case?
-
Yes Steve any entities created under the dbo schema should auto replicate across all the schemas that exist in the database.We have a database with multiple schemas and the reason we want this change one is because developers keep screwing up by creating the object under one schema but not duplicating it under other schemas. So what has been decided is that developers would create objects only under dbo or a custom schema(say dev schema created for them)and the changes they make under their schema should auto replicate across different schemas.
-
You could create a DDL trigger that would be able to duplicate the create table statements in the other schemas. You would want to be sure the DDL trigger only fires once and in the designated development schema,
-
Thanks Lyon. But then how would I have the structure of the underlying object that is getting changed available in the DDL trigger ?
-
Read about DDL triggers, the information you would need is available in the event data returned to the trigger.
-
Now, if you also have to deal with data changes in the tables, that is something completely different and probably shouldn't be done in a DDL trigger.
The other option is to establish a migration process to handle the moves to other schemas.
-
I agree with Lynn. Use a DDL trigger here that will read metadata to build the changes needed for a table to replicate it.
What I'd do is create a process and some code that duplicates the code a developer writes and moves this to other schemas. then give this to the developer. If he/she can't do a better job of doing this, then I'd consider finding a new developer. Spending a lot of time on this to program a solution that's easily fixed by following a process is a little waste of resources.
That being said, if you need all developers to move changes to lots of schemas, I'd think about some text token process that takes a script, changes out the schema, executes it, repeats, etc. This seems like a known process that might be eating up developer productivity unnecessarily if it's regularlyused.
I'd also run checks to look for missing items that haven't been moved. That should be a set of relatively simple queries that you can schedule to run and alert someone.
-
If the synchronization of other schemas to dbo doesn't have to be immediate, a job could be scheduled to sync periodically.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply