10

I'm using Visual Studio 2008 to design a SQL Server Compact Edition database (*.sdf). I had to change the schema of one of my tables adding a new column.

I want to "move" that new column from the bottom to a different position in the field list. How can I do this in the Visual Studio designer?

EDIT: I know that from a pure technical view point the order of the columns doesn't matter. I'm prototyping an application, so I have to change the schema a couple of times. If, for example I have to change the primary key, it would be "ugly" having the most important column at the end. Other developers looking at the code would be confused, I think. It's a matter of esthetics.

splattne
  • 102,760
  • 52
  • 202
  • 249

3 Answers3

6

you can't just move it in the designer. You'll have to create a new column, drop the old one, generate the script and edit the script where it inserts into the temp table from the old table, making the old column value (in the select) go into the new column.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • Thanks for the answer. How can I script a table in VS? I didn't find that option. – splattne Apr 28 '09 at 14:01
  • I'm using SQL Server Management Studio, so its a little different (I don't have VS). I have the "Table Designer" toolbar displayed, and I click on the icon that looks like a scroll with a diskette, and then copy/paste the text from the window that appears. Make sure to cancel out of the operation, so the changes are not run. – KM. Apr 28 '09 at 14:09
  • I must be blind. I can't find the toolbar. Are you sure you used a SQL Server Compact database? – splattne Apr 28 '09 at 15:55
  • I'm not using CTE, and after checking around, you're out of luck for a Microsoft scripting solution, because there isn't one. People have said to use this: http://www.primeworks-mobile.com/Products/DataPortConsole.html – KM. Apr 28 '09 at 18:20
  • Thank you, KMike! I guess the best I can do now, is drop that table and recreate it from scratch. – splattne Apr 28 '09 at 18:32
2

I agree with Pax. If, for a specific reason, you need to return fields in a specific order in your query, just alter the query putting the field in the place where you need it.

If, for whatever reason, you need at all costs to move that field, you can do it with a script like the following, which makes FIELD3 the first column in a table called TestTable:

/* Original sample table with three fields */
CREATE TABLE [dbo].[TestTable](
    [FIELD1] [nchar](10) NULL,
    [FIELD2] [nchar](10) NULL,
    [FIELD3] [nchar](10) NULL
) ON [PRIMARY]

/* The following script will make FIELD3 the first column */
CREATE TABLE dbo.Tmp_TestTable
    (
    FIELD3 nchar(10) NULL,
    FIELD1 nchar(10) NULL,
    FIELD2 nchar(10) NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (FIELD3, FIELD1, FIELD2)
        SELECT FIELD3, FIELD1, FIELD2 FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.TestTable
GO

EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO

However, I insist that probably your problem could be solved with a different approach which doesn't require restructuring table.

Diego
  • 7,312
  • 5
  • 31
  • 38
  • 2
    It's just my anal retentiveness. ;-) I can't look at a table with a column which is the most important at the end... – splattne Apr 28 '09 at 14:15
  • Ok, I hope my suggestion can be of some help then. :) – Diego Apr 28 '09 at 14:25
  • 1
    make sure you copy over all the other table items: index, fk, check constraints, etc... – KM. Apr 28 '09 at 14:51
  • 2
    Hm, I'm looking for a way to script the objects. Seems to be not a trivial task for a SQL Server COMPACT database... – splattne Apr 28 '09 at 15:56
0

Follow these steps:

  1. Remove all foreign keys and primary key of the original table
  2. Rename the original table
  3. Using CTAS create the original table in the order you want
  4. Drop the old table.
  5. Apply all constraints back to the original table
splattne
  • 102,760
  • 52
  • 202
  • 249
Ravichandra Aj
  • 13
  • 3