Ad:

MS SQL Discussion Thread
Unsolved
Views: 2331

Sep 26th, 2008

SQL command to ALTER!! PLease Help

Expand Post »

I have a huge database with around 250 tables. But all the tables are the same ie. they all have all the same columns. These are survery forms from different stores and thats why they all have the same schema. When i converted all these files from excel sheets into one SQL Server 2008 database NO Primary Keys were assigned to any of the columns in any of the tables. Its not practical for me to go into each table and set the primary key. I need to know how i can set the primary keys for the same column in all the tables.

Database name: FAB
all my tables have the following columns:
osd | ood | Price | FPC_Code | FPC_Description | prdabbv

All my columns are of the type varchar(255).
Basically, All the tables in my database look exactly the same as the one above.
FPC_ code is a column that is never NULL and UNIQUE. So ideally i would like to set the FPC_Code column in all my tables as a primary key. Im not that great at SQL scripts so please help me.

Similar Threads

Undefined Element Error in ColdFusion
How can i retrieve data after i logod on..... in ASP.NET
Simple sql questions (broken commands and retrieving columns) in Database Design

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

Permalink

Sep 26th, 2008

Re: SQL command to ALTER!! PLease Help

i would create a stored procedure with a cursor

use this to get the table names

MS SQL Syntax (Toggle Plain Text)
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U'
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U'

and this dynamic sql to set key

MS SQL Syntax (Toggle Plain Text)
exec('alter table ' + @tablename + ' ADD PRIMARY KEY(osd)');
exec('alter table ' + @tablename + ' ADD PRIMARY KEY(osd)');

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 28th, 2008

Re: SQL command to ALTER!! PLease Help

I didn't test this but if I remember right the syntax goes:

VB.NET Syntax (Toggle Plain Text)
Imports System.Data.OleDb
 
Dim strSQL As String
Dim oConn As OleDb.OleDbConnection
Dim oCmd As OleDb.OleDbCommand
 
oConn = New OleDb.OleDbConnection("<connection string>")
strSQL = "ALTER TABLE <table name> ALTER COLUMN FPC_Code CONSTRAINT FPC_Code_PK PRIMARY KEY"
oCmd = New OleDb.OleDbCommand(strSQL, oConn)
oCmd.ExecuteNonQuery()
Imports System.Data.OleDb

Dim strSQL As String
Dim oConn As OleDb.OleDbConnection
Dim oCmd As OleDb.OleDbCommand

oConn = New OleDb.OleDbConnection("<connection string>")
strSQL = "ALTER TABLE <table name> ALTER COLUMN FPC_Code CONSTRAINT FPC_Code_PK PRIMARY KEY"
oCmd = New OleDb.OleDbCommand(strSQL, oConn)
oCmd.ExecuteNonQuery()

and this is for the combined table and assuming FPC_Code is still UNIQUE in the combined table. If you want to add PK for each hundred or so tables, dickersonka's answers gives a good starting point.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Sep 28th, 2008

Re: SQL command to ALTER!! PLease Help

thanx a lot guys.....ill try it out n let ya know....

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: plese helpe maintenceplan for sql2005

Next Thread in MS SQL Forum Timeline: Tricky Select Needed

DaniWeb Message

Cancel Changes

User Name
Password