Home > Articoli > sp_alter_column – La stored procedure per modificare una colonna!

sp_alter_column – La stored procedure per modificare una colonna!

Sarà successo anche a voi di dover modificare il tipo di dato di una colonna presente da tempo in una tabella di un database SQL Server e probabilmente vi sarete scontrati con l’errore 5074 che segnala l’impossibilità di modificare la colonna a causa della presenza di oggetti collegati come Primary Key, Foreign Key, Indici, Vincoli (di univocità e non), Statistiche, ecc..

Ecco un esempio dell’errore 5074:

Msg 5074, Level 16, State 1, Line 1135 - The object 'objectname' is dependent on column 'Columnname'.

Msg 4922, Level 16, State 9, Line 1135 - ALTER TABLE ALTER COLUMN Columnname 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 via script T-SQL.

Come avete risolto il problema?

Alcuni di voi probabilmente avranno eliminato gli oggetti collegati, modificato il tipo di dato della colonna, la dimensione dove prevista, oppure le proprietà e successivamente avrete ricreato gli oggetti precedentemente eliminati facendo molta attenzione a non modificare le proprietà degli oggetti stessi durante le operazioni di DROP e CREATE.

Personalmente, mi sono scontrato diverse volte con questo problema al punto di voler automatizzare l’eliminazione e la creazione degli oggetti collegati ad una colonna per poterne modificare il tipo agevolmente. E’ nata così la Stored Procedure sp_alter_column che ora è disponibile sull’omonimo repository di GitHub.

La versone 1.0 della Stored Procedure sp_alter_column prevede 6 parametri di input:

  • @schemaname rappresenta il nome dello schema in cui risiede la tabella
  • @tablename rappresenta il nome della tabella in cui risiede la colonna che si desidera modificare
  • @columnname rappresenta il nome della colonna che si desidera modificare
  • @columnrename rappresenta l’eventuale nuovo nome che si desidera assegnare alla colonna (by default @columnrename viene assegnato uguale a @columnname)
  • @datatype rappresenta il nuovo tipo di dato da assegnare alla colonna
  • @executionmode rappresenta la modalità di esecuzione (default zero)

Dopo aver controllato i parametri di input, sp_alter_column identifica gli oggetti che che dipendono dalla colonna che si desidera modificare ed in funzione della tipologia genera i comandi di DROP e CREATE per la successiva esecuzione. I comandi T-SQL vengono memorizzati nella tabella temporanea gestita dalla stored procedure. sp_alter_column è in grado di identificare e generare comandi DROP/CREATE per i seguenti oggetti database (che possono avere dipendenze con una colonna):

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

Il parametro @executionmode, di tipo bit, regola la modalità di esecuzione della Stored Procedure. Il valore Zero (default) indica alla sp_alter_column di non applicare le modifiche, ma di visualizzare soltanto i comandi T-SQL necessari per cambiare il tipo di dato oppure il nome della colonna. Questa modalità di esecuzione è particolarmente indicata per familiarizzare con la Stored Procedure oppure quando si desidera avere maggior controllo sui comandi che verranno eseguiti, lasciando alla sp_alter_column solo il compito di generarli. Assegnando il valore Uno (Modalità dispositiva) al parametro @executionmode si indica alla Stored Procedure di applicare le modifiche; si ha quindi la modalità di esecuzione dispositiva. Le modifiche saranno comunque eseguite all’interno di una transazione esplicita, il commit verrà applicato al termine di tutte le operazioni solo se l’esito risulterà essere positivo.

Qualora fosse necessario, al fine di identificare comportamenti anomali nel codice della sp_alter_column (e perché no contribuire anche allo sviluppo direttamente sul repository GitHub), il debug della sp_alter_column è possibile attraverso l’ambiente di sviluppo Microsoft Visual Studio! La prima cosa da fare quando si desidera effettuare il debug di una Stored Procedure è scrivere il comando EXECUTE (EXEC) che ne produce l’esecuzione avendo cura di assegnare ai parametri i valori che possono riprodurre l’anomalia o il caso che si desidera analizzare con il supporto del debugger. Dopo aver preparato il comando di avvio della Stored Procedure, dovremo eseguirlo attraverso l’IDE di Microsoft Visual Studio come descritto di seguito:

  • Eseguire Microsoft Visual Studio
  • Accedere alla finestra “Esplora oggetti di SQL Server” e collegare l’istanza SQL Server che ospita il database nel quale risiede la sp_alter_column
  • Espandere il ramo relativo al database
  • Espandere i rami “Programmazione” e “Stored Procedure” per individuare la sp_alter_column
  • Premere il tasto destro del mouse in corrispondenza della sp_alter_column, verrà visualizzato un menu pop-up, selezionare quindi la voce “Debug Procedura…”. Una nuova pagina di query pronta per eseguire il debug della stored procedure selezionata verrà aperta automaticamente
  • Il puntatore sarà posizionato sulla prima istruzione dello script T-SQL generato dal debugger. Sostituire il comando EXEC generato dal debugger con quello preparato in precedenza avendo cura di assegnare il valore di ritorno della Stored Procedure alla variabile @return_value

Utilizzando l’esecuzione “passo-passo” (tasto funzione F11), sarà possibile eseguire tutte le istruzioni dello script fino al comando EXEC che attiverà la Stored Procedure; successivamente, premendo F11 sul comando EXEC, l’execution pointer verrà trasferito sulla prima istruzione contenuta nella sp_alter_column ed il debug potrà iniziare! Continuando con l’esecuzione “passo-passo” (F11) sarà possibile eseguire tutto il codice T-SQL contenuto nella sp_alter_column.

Compatibilmente con le regole di conversione tra tipi di dato descritte in questo articolo e schematizzate nella tabella seguente, la Stored Procedure sp_alter_column permette di modificare agevolmente il tipo di dato ed il nome di una colonna, provatela! Segnalate eventuali miglioramenti (ce ne sono un paio molto evidenti 🙂 che faro’ a breve) e se volete contribuire allo sviluppo su Github, siete i benvenuti!

Buon divertimento!

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

SQL Saturday Parma 2019 – Sessioni registrate

Alcune sessioni tenute al SQL Saturday Parma 2019 sono state registrate! I primi video pubblicati …