SQL command to ALTER!! PLease Help
 

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.

Re: SQL command to ALTER!! PLease Help
 

i would create a stored procedure with a cursor

use this to get the table names

SELECT name

FROM dbo.sysobjects

WHERE xtype = 'U'

and this dynamic sql to set key

exec('alter table ' + @tablename + ' ADD PRIMARY KEY(osd)');

Re: SQL command to ALTER!! PLease Help
 

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

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.

Re: SQL command to ALTER!! PLease Help
 

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