August 25, 2011 at 8:28 am
Hello,
In the structure of my database I have a database with several schemas. Each schema has the same structures of objects, as the example below:
Database: MyDatabase
Schema: CompanyX
tables
users
activities
events
Schema: CompanyY
tables
users
activities
events
For each company I have the correspondent objects (I'm applying the concept of Shared Multi-Tenant Database Schema with Separate).
My question is how or what tool should I make the updates to the objects (when needed) so that they will be automatically forwarded
for all schemas?
Example: I need to add a field in the users table. How to perform the addition of this field to the table schemas of all users at once?
Thanks in advance ..
August 25, 2011 at 10:08 am
The Above looks to be Bit Difficult,
You Can Use Following Code To Update, Looks Messy But Should Help.
Declare @STR VArchar(Max)
SEt @STR=''
Select @STR=Case When @STR='' then '' else @STR end + 'Update '+a.Name+'.'+B.name+ ' Set ColumnList=C.ColumnList
From OrginalSchemaTableName A
Join '+a.Name+'.'+B.name+ ' B On A.JoinCondition=B.JoiningCondition ;'
from sys.schemas A
Join Sys.objects B On A.schema_id=b.schema_id
Where B.name='CheckIsolationLevel'
and b.name Not in(Select name From Sys.schemas Where name='OriginalTableSchemaName')
Exec(@Str)
Kuldeep Bisht
Simplion Technologies
http://mssqlguide.kuldeepbisht.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply