URGENT!! Need help regarding primary key!!

Expand Post »

I have converted around 395 Excel survey forms to MS SQl database with that many tables. Well, I run a script to assign a primary key on all the tables on my database.First I set the column property to NOT NULL and then i assign the primary key to it. I assign the primary key to a column called 'FPC_Code'. This column contains values such as, _C2348787, _C567489 etc etc......Well, my problem is that THE SECOND I ASSIGN THE PRIMARY KEY TO ALL THE TABLES THEY GET SORTED WRT THE FPC_Code column. I dont want this sorting to take place. I need it to remain unsorted as they are survey forms and would like to preserve the order. I am running out of time and i seriously need help. This is the script I run on all the tables on my database.

ALTERING COLUMN TO NOT NULL

MS SQL Syntax (Toggle Plain Text)
 
DECLARE Alter_tables_cursor CURSOR
   FOR
   SELECT table_name FROM information_schema.TABLES WHERE table_name<>'dtProperties' AND table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  NOT NULL --> '+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ALTER COLUMN FPC_CODE varchar(255) NOT NULL')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor
                            
DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  NOT NULL --> '+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ALTER COLUMN FPC_CODE varchar(255) NOT NULL')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

ASSIGNING PK

MS SQL Syntax (Toggle Plain Text)
DECLARE Alter_tables_cursor CURSOR
   FOR
   SELECT table_name FROM information_schema.TABLES WHERE table_name<>'dtProperties' AND table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  Primary Key -->'+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ADD PRIMARY KEY (FPC_Code)')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor
DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  Primary Key -->'+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ADD PRIMARY KEY (FPC_Code)')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

Last edited by peter_budo; Nov 28th, 2008 at 1:40 pm. Reason: Correcting closing code tags from [\code] to [/code]

Similar Threads

HTML with VB.NET need help urgent in VB.NET
list contraints of access table using vb.net 2005 in VB.NET
Need urgent help. Problem with SQL and win server. in Windows NT / 2000 / XP
Asp.net Sql Query in ASP.NET
How to pass querystring to crystal report in Visual Basic 4 / 5 / 6

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

Permalink

Nov 28th, 2008

Re: URGENT!! Need help regarding primary key!!

SQL Server creates primary keys (by default) as clustered - which means it gets sorted.

Create a NONCLUSTERED primary key and you should be all set.

omrsafetyo

Reputation Points: 13

Solved Threads: 9

Junior Poster in Training

Offline

58 posts
since Apr 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: Return database that a trigger was executed in

Next Thread in MS SQL Forum Timeline: Please help

DaniWeb Message

Cancel Changes

User Name
Password