Comment ALTER plusieurs colonnes à la fois dans SQL Server

Question

J'ai besoin de MODIFIER les types de données de plusieurs colonnes dans une table.

Pour une seule colonne, le code suivant fonctionne bien :

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0) 

Mais comment modifier plusieurs colonnes en une seule instruction ? Le code suivant ne fonctionne pas :

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0)

Answer 1

Si vous ne voulez pas écrire tout vous-même et changer tous les colonnes au même type de données, cela peut vous simplifier la tâche :

select 'alter table tblcommodityOHLC alter column '+name+ 'NUMERIC(18,0);'
from syscolumns where id = object_id('tblcommodityOHLC ')

Vous pouvez copier et coller la sortie en tant que votre requête

Answer 2

Si vous apportez des modifications dans Management Studio et que vous générez des scripts, cela crée une nouvelle table et insère les anciennes données avec les types de données modifiés. Voici un petit exemple de changement de type de données de deux colonnes

/*
   12 août 201008:30:39
   Utilisateur: 
   Serveur: CLPPRGRTEL01\TELSQLEXPRESS
   Base de données: Tracker_3
   Application: 
*/

/* Pour éviter tout problème de perte de données potentiel, vous devez examiner ce script en détail avant de l'exécuter en dehors du contexte du concepteur de base de données.*/
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
ALTER TABLE dbo.tblDiary
    DROP CONSTRAINT FK_tblDiary_tblDiary_events
GO
ALTER TABLE dbo.tblDiary_events SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tblDiary
    (
    Diary_ID int NOT NULL IDENTITY (1, 1),
    Date date NOT NULL,
    Diary_event_type_ID int NOT NULL,
    Notes varchar(MAX) NULL,
    Expected_call_volumes real NULL,
    Expected_duration real NULL,
    Skill_affected smallint NULL
    )  ON T3_Data_2
     TEXTIMAGE_ON T3_Data_2
GO
ALTER TABLE dbo.Tmp_tblDiary SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary ON
GO
IF EXISTS(SELECT * FROM dbo.tblDiary)
     EXEC('INSERT INTO dbo.Tmp_tblDiary (Diary_ID, Date, Diary_event_type_ID, Notes, Expected_call_volumes, Expected_duration, Skill_affected)
        SELECT Diary_ID, Date, Diary_event_type_ID, CONVERT(varchar(MAX), Notes), Expected_call_volumes, Expected_duration, CONVERT(smallint, Skill_affected) FROM dbo.tblDiary WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary OFF
GO
DROP TABLE dbo.tblDiary
GO
EXECUTE sp_rename N'dbo.Tmp_tblDiary', N'tblDiary', 'OBJECT' 
GO
ALTER TABLE dbo.tblDiary ADD CONSTRAINT
    PK_tblDiary PRIMARY KEY NONCLUSTERED 
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2

GO
CREATE UNIQUE CLUSTERED INDEX tblDiary_ID ON dbo.tblDiary
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
CREATE NONCLUSTERED INDEX tblDiary_date ON dbo.tblDiary
    (
    Date
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
ALTER TABLE dbo.tblDiary WITH NOCHECK ADD CONSTRAINT
    FK_tblDiary_tblDiary_events FOREIGN KEY
    (
    Diary_event_type_ID
    ) REFERENCES dbo.tblDiary_events
    (
    Diary_event_ID
    ) ON UPDATE  CASCADE 
     ON DELETE  CASCADE 

GO
COMMIT

Answer 3

sélectionner 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
    ' ALTER COLUMN ' + c.name + ' DATETIME2 ' + 
    CAS LORSQUE c.is_nullable = 0 ALORS 'NON NULL' SINON 'NULL' FIN
depuis sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
où o.type='U'
et c.name = 'Timestamp'
et t.name = 'datetime'
ordre par OBJECT_NAME(o.object_id)

avec l'aimable autorisation de devio

Answer 4

-- créer une table temporaire 
CREATE TABLE temp_table_alter
(
column_name varchar(255)    
);

-- insérer ces colonnes dans la table temporaire pour lesquelles nous devons modifier la taille des colonnes 
INSERT INTO temp_table_alter (column_name) VALUES ('colm1');
INSERT INTO temp_table_alter (column_name) VALUES ('colm2');
INSERT INTO temp_table_alter (column_name) VALUES ('colm3');
INSERT INTO temp_table_alter (column_name) VALUES ('colm4');

DECLARE @col_name_var varchar(255);
DECLARE alter_table_cursor CURSOR FOR
select column_name from temp_table_alter ;

OPEN alter_table_cursor
FETCH NEXT FROM alter_table_cursor INTO @col_name_var
WHILE @@FETCH_STATUS = 0
 BEGIN

 PRINT('ALTER COLUMN ' + @col_name_var);
 EXEC ('ALTER TABLE Original-table  ALTER COLUMN ['+ @col_name_var + '] DECIMAL(11,2);')

 FETCH NEXT FROM alter_table_cursor INTO @col_name_var
 END

CLOSE alter_table_cursor
DEALLOCATE alter_table_cursor

-- à la fin, supprimer la table temporaire
drop table temp_table_alter;

Answer 5

Grâce à l'exemple de code d'Evan, j'ai pu le modifier davantage et le rendre plus spécifique aux tables commençant par, aux noms de colonnes spécifiques ET gérer également des contraintes spécifiques. J'ai exécuté ce code puis j'ai copié la colonne [CODE] et l'ai exécuté sans problème.

USE [Nom_De_La_Table]
GO
SELECT
     TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] DROP CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+']; 
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] datetime2 (7) NOT NULL 
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ADD CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+'] DEFAULT (''3/6/2018 6:47:23 PM'') FOR ['+COLUMN_NAME+']; 
GO' AS '[CODE]'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'form_%' AND TABLE_SCHEMA = 'dbo'
 AND (COLUMN_NAME = 'FormInserted' OR COLUMN_NAME = 'FormUpdated')
 AND DATA_TYPE = 'datetime'