ALTER COLUMN con #sqlcmdcli

Quante volte avete avuto la necessità di modificare il tipo di dato di una colonna da cui dipendono altri oggetti del database SQL Server?

Se avete avuto questa necessità vi sarete scontrati con l’errore numero 5074 che segnala l’impossibilità di modificare tipo di dato e proprietà di una colonna a causa della presenza di oggetti collegati come Indici, Vincoli di univocità, Statistiche, ecc..

Troverete decine di post in rete che descrivono il problema ma pochi che forniscono una soluzione diversa da quella che prevede l’eliminazione manuale degli oggetti collegati prima di eseguire il comando ALTER COLUMN. Agendo in questo modo, dopo aver applicato le modifiche alla colonna sarà necessario ricreare gli oggetti collegati.

L’errore 5074 si presenta sotto questa forma:

Msg 5074, Level 16, State 1, Line 1135

The object 'Object Name' is dependent on column 'Column Name'.

All’errore 5074 seguirà l’errore 4922 in questa forma:

ALTER TABLE ALTER COLUMN 'Column Name' failed because one or more objects access this column

Anche la modifica del nome non è un’operazione “banale”, specialmente se la colonna è referenziata in Viste, Stored Procedure ecc.. Per eseguire il rename di una colonna c’è la Stored Procedure di sistema sp_rename, ma per il cambio del tipo di dato, al netto di eventuali tool di terze parti, non avete altra possibilità se non quella di agire manualmente.

Ho affrontato più volte questo problema e dopo la seconda 🙂 ho pensato bene di realizzare una procedura per automatizzare l’eliminazione e la creazione degli oggetti collegati ad una colonna per poterne modificare il tipo di dato e le proprietà in modo semplice e veloce. È nata così la stored procedure sp_alter_column oggi integrata nella command line application sqlcmdcli.

sqlcmdcli è un progetto opensource che offre la possibilità di effettuare operazioni specifiche su database SQL Server tra cui la modifica del tipo dato di una colonna con dipendenze all’interno del database.

Il comando altercolumn è in grado di identificare gli oggetti che dipendono dalla colonna che si desidera modificare e in funzione del tipo di oggetto è in grado di generare i comandi di DROP e CREATE per i seguenti oggetti database (che possono avere dipendenze con una colonna):

• Primary key
• Foreign key
• Default constraint
• Unique constraint
• Check constraint
• Index
• Statistics
• View (Refresh delle viste che espongono la colonna modificata)

Esempio

Qualora si volesse modificare il tipo di dato della colonna Revision della tabella Production.Document nel database AdventureWorks2017, da nchar(5) a nvarchar(10) con il seguente comando TSQL:

ALTER TABLE Production.Document ALTER COLUMN Revision NVARCHAR(10) NOT NULL

Si riceverà il seguente messaggio di errore:

Messaggio 5074, livello 16, stato 1, riga 5

The index 'IX_Document_FileName_Revision' is dependent on column 'Revision'.

Messaggio 4922, livello 16, stato 9, riga 5

ALTER TABLE ALTER COLUMN Revision failed because one or more objects access this column.

Il comando altercolumn di sqlcmdcli eseguirà l’operazione senza errori, di seguito un esempio della command line:

sqlcmdcli.exe altercolumn -servername:SSS -databasename:DDD -username:UU -password:PP -schemaname:Production -tablename:Document -columnname:Revision -datatype:nvarchar(10)

Conclusioni

Compatibilmente con le regole di conversione tra tipi di dato descritte in questo articolo e schematizzate nella tabella seguente, il comando altercolumn di sqlcmdcli permette di modificare agevolmente il tipo di dato ed il nome di una colonna, provatelo e segnalate eventuali miglioramenti.

Se volete contribuire al progetto, siete i benvenuti!