September 3, 2009 at 7:59 am
ok where do i start i'm a complete novice and have to have this database changed asap.
I'm currently using sql server management studio...using sql server 2005.
i want to create a script that will run through all the tables within sysobjects inserting a new primary key called "company" see below.
company , char(3), default = 'BOX' , not null
so for example the table before would look like the stock example below
PK stock
stock name
after the script has been executed.
PK company
PK stock
stock name
how would i structure the sql to process each table?
alternatively i don't mind putting in each one of the table names if need be....and i need to keep the existing indexes and constraints in place.
appreciatte any help on this.
September 3, 2009 at 8:18 am
I have to question the logic in this..
Is company the true primary key of all table in the DB? And if so then I have to start questioning the entire structure you have.
I see in your example another named PK, you can only have one primary key per table, what is the other one?
CEWII
September 3, 2009 at 8:22 am
You can have two fields in a primary key, but not two primary keys, as Elliot mentioned.
It also makes me wonder if you understand what you need. Does every table really need the company field? I'd question that first, and then start to offer advice.
You also can create a unique index without making this a primary key, but typically company would not be a unique item in all tables.
September 3, 2009 at 9:06 am
Yes the company would become the primary key and the stock the secondary key.
sorry to cause confusion.
September 3, 2009 at 10:04 am
I'm with Steve on this, I don't see how company ALONE can be a primary key on all your tables. You know that primary key is a UNIQUE index/constraint right? If you have a single record in any table that has a duplicate company you can't create that index.. Such a structure would be highly unusual unless you had a bunch of tables that had one-to-one relationships, and that in and of itself would be unusual..
Can you post some DDL for your tables?
CEWII
September 3, 2009 at 11:52 am
I don't think that is what he's asking. This appears to be a multi-tenant shared schema situation, wherein "CompanyID" is the Tenant ID. As such, I believe that he is asking how to :
1) add the column to every table, and
2) populate it (hard to automate), and
3) prefix it to the list of PK columns for each table.
Paul, please confirm is this is correct.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2009 at 12:41 pm
I didin't get that out of that, but you may be right..
CEWII
September 3, 2009 at 12:57 pm
I'm not sure what is being asked.
Do you mean, paulj_p4, that you want a compound key, meaning that companyID becomes part of every primary key, as Barry mentioned? Or do you mean that companyID is a unique key, separate from existing primary keys?
September 3, 2009 at 1:18 pm
This is why I say that:
paulj_p4 (9/3/2009)
...i want to create a script that will run through all the tables within sysobjects inserting a new primary key called "company" see below...so for example the table before would look like the stock example below
PK stock
stock name
after the script has been executed.
PK company
PK stock
stock name
Now that looks like the classic multi-tenant transform.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply