locked
Modifier l'ordre des champs dans une table RRS feed

  • Question

  • Bonjour,

     

    Pour des raisons de maintenance et d'évolution de logiciel je suis amené à ajouter, par programmation, de nouveaux champs dans certaines tables.

    J'y arrive avec ALTER TABLE mais les nouveaux champs se retrouvent en fin de table.

    Pour des raisons de lisibilité, je souhaite replacer ces nouveaux champs à une position optimale.

    J'y arrive avec l'interface graphique de SQL Management Studio Express, mais comment faire par programmation ?

    D'avance merci pour toutes vos suggestions...

     

    Luc

    samedi 15 novembre 2008 10:01

Réponses

  • J'ai obtenu cette excellente réponse depuis le forum US :

     

    One really great feature in Management Studio is the ability to scrip out just about any action you can perform.  When you're designing a table in SSMS, there is a scroll icon with a disk (top left).  Click this, and it will show you the TSQL that will be issued to make the change.  I just tried it for a table in one of my databases, and the script is as follows:

     

    Code Snippet

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_MaintPlanTrace

          (

          RowNumber int NOT NULL IDENTITY (0, 1),

          TextData ntext NULL,

          EventClass int NULL,

          ApplicationName nvarchar(128) NULL,

          NTUserName nvarchar(128) NULL,

          LoginName nvarchar(128) NULL,

          CPU int NULL,

          Reads bigint NULL,

          Writes bigint NULL,

          Duration bigint NULL,

          ClientProcessID int NULL,

          SPID int NULL,

          StartTime datetime NULL,

          EndTime datetime NULL,

          BinaryData image NULL

          )  ON [PRIMARY]

           TEXTIMAGE_ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_MaintPlanTrace ON

    GO

    IF EXISTS(SELECT * FROM dbo.MaintPlanTrace)

           EXEC('INSERT INTO dbo.Tmp_MaintPlanTrace (RowNumber, TextData, EventClass, ApplicationName, NTUserName, LoginName, CPU, Reads, Writes, Duration, ClientProcessID, SPID, StartTime, EndTime, BinaryData)

                SELECT RowNumber, TextData, EventClass, ApplicationName, NTUserName, LoginName, CPU, Reads, Writes, Duration, ClientProcessID, SPID, StartTime, EndTime, BinaryData FROM dbo.MaintPlanTrace WITH (HOLDLOCK TABLOCKX)')

    GO

    SET IDENTITY_INSERT dbo.Tmp_MaintPlanTrace OFF

    GO

    DROP TABLE dbo.MaintPlanTrace

    GO

    EXECUTE sp_rename N'dbo.Tmp_MaintPlanTrace', N'MaintPlanTrace', 'OBJECT'

    GO

    ALTER TABLE dbo.MaintPlanTrace ADD CONSTRAINT

          PK__MaintPlanTrace__060DEAE8 PRIMARY KEY CLUSTERED

          (

          RowNumber

          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

     

    GO

    COMMIT

     

     

    As you can see, it creates a (permanent) temp table, fetches all of the data into said temp table, and then renames the temporary structure back to the original table name.

     

    It will take a long time to execute if you have any significant volume of data in your table, so only use this if it really is necessary.  Field order does not affect data processing (if you're using explicit select and insert lists, which you should be), so the tables really should be left alone if at all possible.

     

    Does this help?




    Aaron Alton

    Un grand merci à Aaron Alton !

     

    lundi 17 novembre 2008 19:00