Member Avatar for isaackhazi

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.

  • 3 Contributors
  • 3 Replies
  • 534 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by isaackhazi

Recommended Answers

All 3 Replies

Member Avatar for dickersonka

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)');
Member Avatar for Teme64

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.

Member Avatar for isaackhazi

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.