MS SQL

SQL command to ALTER!! PLease Help

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2008

Posts: 22

Reputation: isaackhazi is an unknown quantity at this point

Solved Threads: 0

isaackhazi isaackhazi is offline

Offline

Newbie Poster

SQL command to ALTER!! PLease Help

Sep 26th, 2008

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.

•

Join Date: Aug 2008

Posts: 1,158

Reputation: dickersonka will become famous soon enough

Solved Threads: 136

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: SQL command to ALTER!! PLease Help

Sep 26th, 2008

i would create a stored procedure with a cursor

use this to get the table names

 Help with Code Tags 
 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

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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Aug 2008

Posts: 710

Reputation: Teme64 will become famous soon enough

Solved Threads: 114

Teme64

Offline

Master Poster

Re: SQL command to ALTER!! PLease Help

Sep 28th, 2008

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

 Help with Code Tags 
 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 @ Windows Developer Blog