Create objects under multiple schemas

  • sac.nan

    Old Hand

    Points: 384

    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.

  • pietlinden

    SSC Guru

    Points: 62358

    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?

  • sac.nan

    Old Hand

    Points: 384

    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.

  • Mark Cowne

    One Orange Chip

    Points: 26684

    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
  • sac.nan

    Old Hand

    Points: 384

    Unfortunately synonyms won't work in my case..

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714311

    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?

     

  • sac.nan

    Old Hand

    Points: 384

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442098

    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,

  • sac.nan

    Old Hand

    Points: 384

    Thanks Lyon. But then how would I have the structure of the underlying object that is getting changed available in the DDL trigger ?

  • Lynn Pettis

    SSC Guru

    Points: 442098

    Read about DDL triggers, the information you would need is available in the event data returned to the trigger.

     

  • Lynn Pettis

    SSC Guru

    Points: 442098

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714311

    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.

  • Joe Torre

    SSChampion

    Points: 10225

    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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply