Modificare uno User Defined Type

Gli User Defined Type di SQL Server rappresentano un valido strumento per la definizione di strutture dati a design time, ma purtroppo non possono essere modificati a run time.

Purtroppo, per quanto si possa fare attenzione durante il design di un DB, capita di accorgersi che il tipo utilizzato per una certa classe di colonne, richiede di essere modificato ad esempio da int a bigint o da varchar a nvarchar o semplicemente da nvarchar(64) a nvarchar(128).
Se questa modifica viene fatta a design time, avendo utilizzato gli User Defined Types, sono sufficienti 10 secondi e non si rischia di commettere errori, mentre con i tipi nativi le modifiche ed i rischi possono essere considerevoli.

La prima volta che ho utilizzato gli User Defined Types, sono rimasto molto deluso nello scoprire che una volta creato il DB, non esisteva il modo per eseguire l' Alter dei tipi.

Che si siano utilizzati i tipi nativi o gli UserType, occorre eseguire degli "ALTER TABLE ALTER COLUMN", per tutte le colonne passando, comunque, al tipo nativo e a modificare conseguentemente tutte le SP e funzioni.

Benchè a design time gli User Defined Types siano molto utili, con un DB già esistente, non solo non sono d'aiuto, ma se non si volesse passare al tipo nativo, si dovrebbe creare uno UserType diverso:
   es: 1) Creare ETuObjBigPK = bigint
         2) Alterare ETuObjectPK --> ETuObjBigPK
Se poi si volesse conservare il primo UserType, sarebbe necessario eseguire 2 volte l'operazione:
   es: 1) Creare ETuObjBigPK = bigint
         2) Alterare ETuObjectPK --> ETuObjBigPK
         3) Modificare ETuObjectPK->bigint
         4) Alterare ETuObjBigPK --> ETuObjectPK
Tutto questo, inoltre richiederebbe il Drop e re-Create di SP e funzioni!

Aspettando che MS aggiunga la funzionalità di "ALTER USERTYPE", ho sviluppato una Stored Procedure (e alcune funzioni ausiliarie) che dovrebbe consentire di eseguire il lavoro in modo automatico.
In base ai miei test, sembra funzionare bene, ma ovviamente non ho potuto provarlo in tutte le possibili situazioni...

La funzione ETfGetIndexColumns ritorna le colonne di un indice.
La funzione ETfGetIndexDef ritorna la definizione di un indice.
La funzione ETfGetEmptyFuncDef ritorna la definizione di una funzione svuotata del codice.

Per poter eseguire gli ALTER delle colonne occorre operare anche su tutti gli oggetti correlatii come SP, Funzioni, Viste, Checks, FK, PK e bisogna farlo seguendo un ordine preciso; il tutto viene complicato dal fatto che ad esempio ci possono essere viste dipendenti da una funzione e funzioni dipendenti da una vista...insomma c'è da divertirsi.

Questa SP, in sintesi, dopo averne salvato la definizione, droppa tutti gli oggetti che dipendono dallo UserType, lo modifica e ripristina tutti gli oggetti precedentemente droppati.

Per eseguire il lavoro, ho utilizzato una tabella temporanea in cui inserisco tutti gli script che dovranno essere eseguiti, poi tramite cursori la scorro dal primo all'ultimo record per droppare e in ordine inverso per restorare gli oggetti.

Alcuni oggetti che dichiarano delle variabili di tipo TABLE con campi di tipo "UserType" non possono essere gestiti sotto transazione perchè provocano una sorta di deadlock; in questo caso occorre eseguire la SP con l'opzione @UnderTran = 0.

Le operazioni che devono essere eseguite dalla SP sono le seguenti:

01) Recuperare il tipo nativo dello UserType
02) Ottenere l'elenco dei campi che necessitano di essere modificati
03) Ottenere l'elenco delle viste ordinate in base alle dipendenze
04) Ottenere l'elenco delle SP ordinate in base alle dipendenze
05) Creare tabella per gli script che si dovranno eseguire
06) Inserire gli script per droppare e ricreare le SP
07) Inserire gli script per droppare e ricreare i Check Constraints
08) Inserire gli script per alterare le Funzioni (svuotare e ripristinare)
09) Inserire gli script per droppare e ricreare le Viste
10) Inserire gli script per droppare e ricreare le Funzioni
11) Inserire gli script per droppare e ricreare le Foreign Key
12) Inserire gli script per droppare e ricreare gli Indici
13) Inserire gli script per droppare e ricreare le Primary Key
14) Inserire gli script per alterare Colonne
15) Inserire gli script per eliminare e ri-creare lo UserType
16) Aprire la transazione (se @UnderTran = 1)
17) Scorrere la tabella degli script per Drop Oggetti
18) Scorrere la tabella degli script per Ripristinare gli Oggetti
19) Chiudere la transazione (se @UnderTran = 1)
20) Droppare le tabelle temporanee

Esempi di Utilizzo:

-- Stampa tutti gli script che sono necessari
-- Le SP lunghe potrebbero non essere complete
EXEC ETpAlterUserDefType N'ETuObjectPK', N'bigint', N'PRINT'

-- Ritorna un dataset con tutti gli script necessari
EXEC ETpAlterUserDefType N'ETuObjectPK', N'bigint', N'SELECT'

-- Esegue l'operazione sotto transazione
-- per consentire il roolback in caso di errore
EXEC ETpAlterUserDefType N'ETuObjectPK', N'bigint', N'EXEC'

-- Esegue l'operazione senza transazione
-- per consentire la modifica delle funzioni "Tabella"
EXEC ETpAlterUserDefType N'ETuObjectPK', N'bigint', N'EXEC', 0

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETfGetIndexColumns]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[ETfGetIndexColumns]
GO

CREATE FUNCTION [dbo].[ETfGetIndexColumns](@SchName sysname, @TabName sysname, @IdxName sysname)
RETURNS Nvarchar(255)
AS
BEGIN
DECLARE @Col sysname
DECLARE @Cols Nvarchar(512)

    SET @Cols = N''

    SELECT @Cols = @Cols + N', ' + c.name
      FROM sys.indexes X 
            INNER JOIN sys.tables T ON X.object_id = T.object_id
            INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
            INNER JOIN sys.index_columns XC ON XC.object_id = T.object_id AND XC.index_id = X.index_id
            INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = XC.column_id
     WHERE S.name = @SchName
       AND T.name = @TabName
       AND X.name = @IdxName
       AND XC.key_ordinal > 0
     order by XC.key_ordinal 

    RETURN SUBSTRING(@Cols, 3, 255)
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETfGetIndexDef]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[ETfGetIndexDef]
GO

CREATE FUNCTION [dbo].[ETfGetIndexDef](@SchName sysname, @TabName sysname, @IdxName sysname)
RETURNS NVarChar(2048)
AS
BEGIN
DECLARE @Cols Nvarchar(1024)
DECLARE @Incs Nvarchar(1024)
DECLARE @Stat Nvarchar(255)
DECLARE @FGrp Nvarchar(255)
DECLARE @IDef Nvarchar(2048)

	SET @Cols = N''
	SELECT @Cols = @Cols + c.name + N','
      FROM sys.indexes X 
            INNER JOIN sys.tables T ON X.object_id = T.object_id
            INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
            INNER JOIN sys.index_columns XC ON XC.object_id = T.object_id AND XC.index_id = X.index_id
            INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = XC.column_id
     WHERE S.name = @SchName
       AND T.name = @TabName
       AND X.name = @IdxName
       AND XC.key_ordinal > 0
     ORDER BY XC.key_ordinal 

	If @Cols <> N'' SET @Cols = N'(' + Left(@Cols, Len(@Cols)-1) + N')'

	SET @Incs = N''
	SELECT @Incs = @Incs + c.name + N','
      FROM sys.indexes X 
            INNER JOIN sys.tables T ON X.object_id = T.object_id
            INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
            INNER JOIN sys.index_columns XC ON XC.object_id = T.object_id AND XC.index_id = X.index_id
            INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = XC.column_id
     WHERE S.name = @SchName
       AND T.name = @TabName
       AND X.name = @IdxName
       AND XC.key_ordinal = 0
     ORDER BY XC.key_ordinal 

	If @Incs <> N'' SET @Incs = N' INCLUDE(' +  Left(@Incs, Len(@Incs)-1) + N')'

	SET @Stat = N''
	SELECT @Stat = N'UNIQUE ' 
	  FROM sys.indexes X 
	        INNER JOIN sys.objects O on X.object_id = O.object_id
            INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
     WHERE S.name = @SchName
       AND O.name = @TabName
	   AND X.name = @IdxName
	   AND X.is_Unique = 1

	SET @FGrp = N''
	SELECT @FGrp = g.name 
	  FROM sys.indexes X 
	        INNER JOIN sys.objects O on X.object_id = O.object_id
            INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
			INNER JOIN sys.filegroups G on X.data_space_id = G.data_space_id
     WHERE S.name = @SchName
       AND O.name = @TabName
	   AND X.name = @IdxName

	If @FGrp <> N'' SET @FGrp = N' ON [' +  @FGrp + N']'

	SET @IDef = N'CREATE ' + @Stat + N'INDEX [' + @IdxName + N'] ON [' + @SchName + N'].[' + @TabName + '] ' + 
				 + @Cols + @Incs + @FGrp
	
	RETURN @IDef
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETfGetEmptyFuncDef]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[ETfGetEmptyFuncDef]
GO

CREATE FUNCTION [dbo].[ETfGetEmptyFuncDef](@SchName sysname, @ObjName sysname, @ToAlter bit)
RETURNS NVarChar(2048)
AS
BEGIN
DECLARE @FType char(2)
DECLARE @FDefn nvarchar(max)
DECLARE @Parms nvarchar(1000)
DECLARE @FBody nvarchar(1000)

    SELECT @FType = O.type, @FDefn = M.definition
      FROM sys.objects O
            INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
            INNER JOIN sys.sql_modules M ON M.object_id = O.object_id
     WHERE S.name = @SchName
       AND O.name = @ObjName
    
    SET @Parms = N''

    SELECT @Parms = @Parms + P.name + N' ' + T.name + 
            Case 
                When T.name In (N'char', N'varchar', N'binary', N'varbinary')
                    Then N'(' + Cast(P.max_length As nvarchar(8)) + N')'
                When T.name In (N'nchar', N'nvarchar') And P.max_length = -1
                    Then N'(max)'
                When T.name In (N'nchar', N'nvarchar')
                    Then N'(' + Cast(P.max_length/2 As nvarchar(8)) + N')'
                Else N'' 
            End + N', '
      FROM sys.parameters P
            INNER JOIN sys.objects O ON O.object_id = P.object_id
            INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
            INNER JOIN sys.types T ON T.user_type_id = P.user_type_id
     WHERE P.is_output = 0
       AND S.name = @SchName
       AND O.name = @ObjName

	IF @Parms <> N'' 
	    SET @Parms = N'(' + Left(@Parms, Len(@Parms)-1) + N')'
	ELSE
	    SET @Parms = N'()'

    SET @FBody = N'CREATE FUNCTION [' + @SchName + N'].[' + @ObjName + N']' + @Parms
    
    IF @FType = N'IF'
        SET @FBody = @FBody + N' RETURNS TABLE AS RETURN SELECT NULL AS X'
    ELSE IF @FType = N'TF'
        SET @FBody = SUBSTRING(@FDefn, 1, CHARINDEX(N'BEGIN', @FDefn)+5) + N' RETURN END'
    ELSE
        SELECT @FBody = @FBody + N' RETURNS ' + T.name +
                            Case 
                                When T.name In (N'char', N'varchar', N'binary', N'varbinary')
                                    Then N'(' + Cast(P.max_length As nvarchar(8)) + N')'
                                When T.name In (N'nchar', N'nvarchar') And P.max_length = -1
                                    Then N'(max)'
                                When T.name In (N'nchar', N'nvarchar')
                                    Then N'(' + Cast(P.max_length/2 As nvarchar(8)) + N')'
                                Else N'' 
                            End + N' AS BEGIN RETURN NULL END'
          FROM sys.parameters P
                INNER JOIN sys.objects O ON O.object_id = P.object_id
                INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
                INNER JOIN sys.types T ON T.user_type_id = P.user_type_id
         WHERE P.is_output = 1
           AND S.name = @SchName
           AND O.name = @ObjName

    IF @ToAlter = 1
        SET @FBody = REPLACE(@FBody, N'CREATE ', N'ALTER ')

    RETURN @FBody
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETpAlterUserDefType]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[ETpAlterUserDefType]
GO
-- @WorkMode = EXEC / PRINT / SELECT
CREATE PROCEDURE [dbo].[ETpAlterUserDefType](@UserType sysname, @NewType sysname, @WorkMode varchar(16) = 'EXEC', @UnderTran bit = 1)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
DECLARE @BaseType sysname

Declare @DepOrdViews       TABLE(DepLevel int, object_id int)
Declare @NeedAltProcedures TABLE(object_id int)
Declare @DepOrdProcedures  TABLE(DepLevel int, object_id int)

DECLARE @ObjectType sysname
DECLARE @ObjectID   int
DECLARE @SchemaName sysname
DECLARE @ObjectName sysname
DECLARE @ParentName sysname
DECLARE @ObjSqlDefn nvarchar(max)
DECLARE @ObjSqlDrop nvarchar(max)

    SET @Err = 0
    
    -- Get The base Type
    SELECT @BaseType = B.name
      FROM sys.types T
                JOIN sys.types B ON B.system_type_id = T.system_type_id
     WHERE T.name = @UserType
       AND B.system_type_id = B.user_type_id


    -- Get the list of fields needing to be altered
    SELECT T.object_id As TabId, T.name As TabName, C.column_id As FldId, C.name As FldName
    INTO #FldList
      FROM sys.tables T 
             JOIN sys.columns C ON C.object_id = T.object_id
             JOIN sys.types Y ON Y.user_type_id = C.user_type_id
     WHERE Y.name = @UserType
    ORDER BY T.name, C.column_id

    
    -- Get the list of views ordered by dependency
    INSERT @DepOrdViews
    SELECT DISTINCT 0 AS DepLevel, V.object_id
      FROM sys.views V
             LEFT JOIN (sys.sql_dependencies DT 
                            INNER JOIN sys.tables TR ON TR.object_id = DT.referenced_major_id)
                  ON V.object_id = DT.object_id
             LEFT JOIN (sys.sql_dependencies DV INNER JOIN sys.views VR ON VR.object_id = DV.referenced_major_id)
                  ON V.object_id = DV.object_id
     WHERE TR.name IN (Select Distinct TabName From #FldList)
       AND VR.object_id IS NULL

    WHILE @@ROWCOUNT > 0
        INSERT @DepOrdViews
        SELECT DISTINCT OV.DepLevel+1 AS DepLevel, V.object_id
          FROM @DepOrdViews OV 
                INNER JOIN sys.sql_dependencies R ON OV.object_id = R.referenced_major_id 
                INNER JOIN sys.views V ON V.object_id = R.object_id
         WHERE OV.DepLevel = (Select MAX(DepLevel) From @DepOrdViews)

    
    -- Get the list of SPs ordered by dependency
    INSERT @NeedAltProcedures
    SELECT P.object_id
      FROM sys.procedures P
            INNER JOIN sys.sql_modules M ON M.object_id = P.object_id
     WHERE CHARINDEX(@UserType, M.definition) > 0

    INSERT @DepOrdProcedures
    SELECT DISTINCT 0 AS DepLevel, P.object_id
      FROM @NeedAltProcedures P
             LEFT JOIN (sys.sql_dependencies DP 
                          INNER JOIN @NeedAltProcedures PR ON DP.referenced_major_id = PR.object_id) 
                   ON P.object_id = DP.object_id
     WHERE PR.object_id IS NULL

    WHILE @@ROWCOUNT > 0
        INSERT @DepOrdProcedures
        SELECT DISTINCT OP.DepLevel+1 AS DepLevel, P.object_id
          FROM @DepOrdProcedures OP 
                INNER JOIN sys.sql_dependencies R ON OP.object_id = R.referenced_major_id 
                INNER JOIN @NeedAltProcedures P ON P.object_id = R.object_id
         WHERE OP.DepLevel = (Select MAX(DepLevel) From @DepOrdProcedures)
           AND R.object_id <> R.referenced_major_id


    -- Backup Table for items to be dropped and restored
    CREATE TABLE #ObjectsScripts(ObjIDX int IDENTITY(1,1), 
                                 ObjType nvarchar(16),
                                 ObjectID int,
                                 SchemaName sysname, 
                                 ObjectName sysname,
                                 ObjSqlDefn nvarchar(max),
                                 ObjSqlDrop nvarchar(max))


    -- BackUp SPs and generate script to drop them
    DECLARE SpDCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT P.object_id, S.name, P.name, M.definition
          FROM (Select MAX(DepLevel) DepLevel, object_id From @DepOrdProcedures Group By object_id) DP
                INNER JOIN sys.procedures P ON P.object_id = DP.object_id
                INNER JOIN sys.schemas S ON S.schema_id = P.schema_id
                INNER JOIN sys.sql_modules M ON M.object_id = P.object_id
         ORDER BY DP.DepLevel DESC

    OPEN SpDCur
        FETCH NEXT FROM SpDCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDrop = N'If Exists (Select * From sys.objects Where object_id = object_id(N''[' + @SchemaName + N'].[' + @ObjectName + N']'') and type in (N''P'', N''PC''))' + Char(13) + Char(10) + 
		                    Char(9) + N'DROP PROCEDURE [' + @SchemaName + N'].[' + @ObjectName + N']' + Char(13) + Char(10);
            INSERT #ObjectsScripts VALUES(N'P', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM SpDCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        END
    CLOSE SpDCur
    DEALLOCATE SpDCur


    -- Generate script to Reset and Restore Check Constraints
    -- Must be done before Functions because CK may use a funtion!
    DECLARE ChkCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT K.object_id, S.name, C.name, 
               Case When D.name Is Not NULL Then 
                N'ALTER TABLE [' + S.name + N'].[' + T.name + N']' + Char(13) + Char(10) +
                Char(9) + N'ADD CONSTRAINT [' + D.name + N'] DEFAULT ' + D.definition + N' FOR ' + F.FldName + N';' + Char(13) + Char(10) Else N'' End +
               Case When K.name Is Not NULL Then 
                N'IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N''[' + S.name + N'].[' + K.name + N']'') AND parent_object_id = OBJECT_ID(N''[' + S.name + N'].[' + T.name + N']''))' + Char(13) + Char(10) + 
                N'  ALTER TABLE [' + S.name + N'].[' + T.name + N']' + Char(13) + Char(10) +
                Char(9) + N'WITH NOCHECK ADD CONSTRAINT [' + K.name + N'] CHECK ' + K.definition Else N'' End +
                Char(13) + Char(10) As ObjSqlDefn,

               Case When K.name Is Not NULL Then 
                N'IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N''[' + S.name + N'].[' + K.name + N']'') AND parent_object_id = OBJECT_ID(N''[' + S.name + N'].[' + T.name + N']''))' + Char(13) + Char(10) + 
                N'  ALTER TABLE [' + S.name + N'].[' + T.name + N']' + Char(13) + Char(10) + 
                Char(9) + N'DROP CONSTRAINT [' + K.name + N']; ' + Char(13) + Char(10) Else N'' End +
               Case When D.name Is Not NULL Then 
                N'ALTER TABLE [' + S.name + N'].[' + T.name + N']' + Char(13) + Char(10) + 
                Char(9) + N'DROP CONSTRAINT [' + D.name + N'] ' Else N'' End +
                Char(13) + Char(10) As ObjSqlDrop
          FROM #FldList F
                  INNER JOIN sys.tables T ON F.TabId = T.object_id
                  INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
                  INNER JOIN sys.columns C ON C.object_id = F.TabId AND C.column_id = F.FldId
                   LEFT JOIN sys.default_constraints D ON D.parent_object_id = C.object_id AND D.parent_column_id = C.column_id
                   LEFT JOIN sys.check_constraints K ON K.parent_object_id = C.object_id AND CHARINDEX(F.FldName, K.definition) > 0
          WHERE D.name Is Not NULL OR K.name Is Not NULL

    OPEN ChkCur
        FETCH NEXT FROM ChkCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT #ObjectsScripts VALUES(N'CK', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM ChkCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop
        END
    CLOSE ChkCur
    DEALLOCATE ChkCur


    -- BackUp Functions (As Alter) and generate script to alter them to "Fake Functions"
    -- Fake Functions is needed to create Views
    DECLARE FunDCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT F.type, F.object_id, F.SchName, F.name, M.definition
          FROM (
                SELECT O.type, O.object_id, S.name As SchName, O.name
                  FROM sys.sql_modules M
                        INNER JOIN sys.objects O ON O.object_id = M.object_id
                        INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
                 WHERE O.type in (N'FN', N'TF', N'IF')
                   AND CHARINDEX(@UserType, definition) > 0
                UNION
                SELECT DISTINCT F.type, F.object_id, S.name As SchName, F.name
                  FROM sys.sql_dependencies D
                        INNER JOIN sys.objects F ON F.object_id = D.object_id AND F.type in (N'FN', N'TF', N'IF')
                        INNER JOIN sys.tables T ON T.object_id = D.referenced_major_id
                        INNER JOIN sys.schemas S ON S.schema_id = F.schema_id
                 WHERE T.name IN (Select Distinct TabName From #FldList)
                ) F         
                INNER JOIN sys.sql_modules M ON M.object_id = F.object_id

    OPEN FunDCur
        FETCH NEXT FROM FunDCur INTO @ObjectType, @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDefn = REPLACE(@ObjSqlDefn, N'CREATE ', N'ALTER ')
            SET @ObjSqlDrop = [dbo].[ETfGetEmptyFuncDef](@SchemaName, @ObjectName, 1) --ALTER
            INSERT #ObjectsScripts VALUES(@ObjectType, @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM FunDCur INTO @ObjectType, @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        END
    CLOSE FunDCur


    -- BackUp Views and generate script to drop them
    DECLARE VwDCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT V.object_id, S.name, V.name, M.definition
          FROM (Select MAX(DepLevel) DepLevel, object_id From @DepOrdViews Group By object_id) DV
                INNER JOIN sys.views V ON V.object_id = DV.object_id
                INNER JOIN sys.schemas S ON S.schema_id = V.schema_id
                INNER JOIN sys.sql_modules M ON M.object_id = V.object_id
         ORDER BY DV.DepLevel DESC
         
    OPEN VwDCur
        FETCH NEXT FROM VwDCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDrop = N'If Exists (Select * From sys.views Where object_id = object_id(N''[' + @SchemaName + N'].[' + @ObjectName + N']''))' + Char(13) + Char(10) + 
		                    Char(9) + N'DROP VIEW [' + @SchemaName + N'].[' + @ObjectName + N']' + Char(13) + Char(10);
            INSERT #ObjectsScripts VALUES(N'V', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM VwDCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        END
    CLOSE VwDCur
    DEALLOCATE VwDCur


    -- Backup "Fake Functions" and generate script to drop Functions
    OPEN FunDCur
        FETCH NEXT FROM FunDCur INTO @ObjectType, @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDefn = [dbo].[ETfGetEmptyFuncDef](@SchemaName, @ObjectName, 0) --CREATE
            SET @ObjSqlDrop = N'If Exists (Select * From sys.objects Where object_id = object_id(N''[' + @SchemaName + N'].[' + @ObjectName + N']'') and type in (N''FN'', N''TF'', N''IF''))' + Char(13) + Char(10) + 
		                    Char(9) + N'DROP FUNCTION [' + @SchemaName + N'].[' + @ObjectName + N']' + Char(13) + Char(10);
            INSERT #ObjectsScripts VALUES(@ObjectType, @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM FunDCur INTO @ObjectType, @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn
        END
    CLOSE FunDCur
    DEALLOCATE FunDCur


    -- BackUp Foreign Keys and generate script to drop them
    DECLARE FkDCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT FK.object_id, S.name, FK.name, P.TabName,
                N'If Not Exists (Select * From dbo.sysobjects Where id = object_id(N''[' + S.name + N'].[' + FK.name + N']'') and OBJECTPROPERTY(id, N''IsForeignKey'') = 1)' + Char(13) + Char(10) + 
		          Char(9) + N'ALTER TABLE [' + S.name + N'].[' + P.TabName + N'] WITH NOCHECK ADD CONSTRAINT [' + FK.name + N']' + Char(13) + Char(10) + 
		          Char(9) + Char(9) + N'FOREIGN KEY([' + CP.name + N']) REFERENCES [' + SR.name + N'].[' + R.TabName + N'] ([' + CR.name + N'])' + Char(13) + Char(10) + 
		          case FK.delete_referential_action when 1 then Char(9) + Char(9) + Char(9) + N'ON DELETE CASCADE' + Char(13) + Char(10) Else '' End +
		          case FK.update_referential_action when 1 then Char(9) + Char(9) + Char(9) + N'ON UPDATE CASCADE' + Char(13) + Char(10) Else '' End + 
		          Char(13) + Char(10) 
		  FROM sys.foreign_keys FK 
	            INNER JOIN sys.foreign_key_columns FKC On FK.object_id = FKC.constraint_object_id
	             LEFT JOIN #FldList P ON FK.parent_object_id = P.TabId AND FKC.parent_column_id = P.FldId
	             LEFT JOIN #FldList R ON FK.Referenced_object_id = R.TabId AND FKC.referenced_column_id = R.FldId
                INNER JOIN sys.tables T ON FK.parent_object_id = T.object_id
                INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
                INNER JOIN sys.tables TR ON FK.Referenced_object_id = TR.object_id
                INNER JOIN sys.schemas SR ON SR.schema_id = TR.schema_id
		        INNER JOIN sys.columns CP On FKC.parent_object_id = CP.object_id And FKC.parent_column_id = CP.column_id
		        INNER JOIN sys.columns CR On FKC.referenced_object_id = CR.object_id And FKC.referenced_column_id = CR.column_id
         WHERE P.TabName IS NOT NULL 
            OR R.TabName IS NOT NULL 

    OPEN FkDCur
        FETCH NEXT FROM FkDCur INTO @ObjectID, @SchemaName, @ObjectName, @ParentName, @ObjSqlDefn
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDrop = N'If Exists (Select * From dbo.sysobjects Where id = object_id(N''[' + @SchemaName + N'].[' + @ObjectName + N']'') and OBJECTPROPERTY(id, N''IsForeignKey'') = 1)' + Char(13) + Char(10) + 
		                    Char(9) + N'ALTER TABLE [' + @SchemaName + N'].[' + @ParentName + N'] DROP CONSTRAINT [' + @ObjectName + N']' + Char(13) + Char(10)
            INSERT #ObjectsScripts VALUES(N'K', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM FkDCur INTO @ObjectID, @SchemaName, @ObjectName, @ParentName, @ObjSqlDefn
        END
    CLOSE FkDCur
    DEALLOCATE FkDCur


    -- BackUp Indexes and generate script to drop them
    DECLARE IdxDCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT X.object_id, S.name, X.name, T.name
          FROM sys.indexes X 
                INNER JOIN sys.tables T ON X.object_id = T.object_id
                INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
                INNER JOIN sys.index_columns XC ON XC.object_id = T.object_id AND XC.index_id = X.index_id
                INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = XC.column_id
         WHERE C.name IN (Select FldName From #FldList)
           AND X.is_primary_key = Cast(0 as bit) /* No PK */
           AND X.is_unique_constraint = Cast(0 as bit)  /* No Unique Check */

    OPEN IdxDCur
        FETCH NEXT FROM IdxDCur INTO @ObjectID, @SchemaName, @ObjectName, @ParentName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDefn = N'If Not Exists (Select * From dbo.sysindexes Where Name = ''' + @ObjectName + N''' and id = object_id(N''[' + @SchemaName + N'].[' + @ParentName + N']''))' + Char(13) + Char(10) + 
                              Char(9) + dbo.ETfGetIndexDef(@SchemaName, @ParentName, @ObjectName) + Char(13) + Char(10);
            SET @ObjSqlDrop = N'If Exists (Select * From dbo.sysindexes Where Name = ''' + @ObjectName + N''' and id = object_id(N''[' + @SchemaName + N'].[' + @ParentName + N']''))' + Char(13) + Char(10) + 
		                      Char(9) + N'DROP INDEX [' + @SchemaName + N'].[' + @ParentName + N'].[' + @ObjectName + N']' + Char(13) + Char(10);
            INSERT #ObjectsScripts VALUES(N'I', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM IdxDCur INTO @ObjectID, @SchemaName, @ObjectName, @ParentName
        END
    CLOSE IdxDCur
    DEALLOCATE IdxDCur


    -- BackUp PK constraints and generate script to drop them
    DECLARE PkDCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT DISTINCT X.object_id, S.name, X.name, T.name
          FROM sys.indexes X 
                INNER JOIN sys.tables T ON X.object_id = T.object_id
                INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
                INNER JOIN sys.index_columns XC ON XC.object_id = T.object_id AND XC.index_id = X.index_id
                INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = XC.column_id
                INNER JOIN #FldList F ON C.object_id = F.TabId AND C.column_id = F.FldId
         WHERE X.is_primary_key = Cast(1 as bit)

    OPEN PkDCur
        FETCH NEXT FROM PkDCur INTO @ObjectID, @SchemaName, @ObjectName, @ParentName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ObjSqlDefn = N'ALTER TABLE [' + @SchemaName + N'].[' + @ParentName + N'] ADD CONSTRAINT' + Char(13) + Char(10) + 
                              Char(9) + @ObjectName + N' PRIMARY KEY CLUSTERED (' + dbo.ETfGetIndexColumns(@SchemaName, @ParentName, @ObjectName) + N')' + Char(13) + Char(10)
            SET @ObjSqlDrop = N'ALTER TABLE [' + @SchemaName + N'].[' + @ParentName + N']' + Char(13) + Char(10) + 
                              Char(9) + N'DROP CONSTRAINT ' + @ObjectName + Char(13) + Char(10)
            INSERT #ObjectsScripts VALUES(N'PK', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM PkDCur INTO @ObjectID, @SchemaName, @ObjectName, @ParentName
        END
    CLOSE PkDCur
    DEALLOCATE PkDCur


    -- Generate script to Reset and Restore Columns
    DECLARE ColCur CURSOR LOCAL FAST_FORWARD FOR
        SELECT K.object_id, S.name, C.name, 
               N'ALTER TABLE [' + S.name + N'].[' + T.name + N']' + Char(13) + Char(10) + 
                Char(9) + N'ALTER COLUMN ' + C.name + N' ' + @UserType + 
                Case When C.is_nullable = 1 Then N' NULL' Else N' NOT NULL' End + 
                Char(13) + Char(10) As ObjSqlDefn,

               N'ALTER TABLE [' + S.name + N'].[' + T.name + N']' + Char(13) + Char(10) + 
                Char(9) + N'ALTER COLUMN ' + C.name + N' ' + @BaseType + 
                Case When C.is_nullable = 1 Then N' NULL' Else N' NOT NULL' End + 
                Char(13) + Char(10) As ObjSqlDrop
          FROM #FldList F
                  INNER JOIN sys.tables T ON F.TabId = T.object_id
                  INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
                  INNER JOIN sys.columns C ON C.object_id = F.TabId AND C.column_id = F.FldId
                   LEFT JOIN sys.default_constraints D ON D.parent_object_id = C.object_id AND D.parent_column_id = C.column_id
                   LEFT JOIN sys.check_constraints K ON K.parent_object_id = C.object_id AND CHARINDEX(F.FldName, K.definition) > 0

    OPEN ColCur
        FETCH NEXT FROM ColCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT #ObjectsScripts VALUES(N'CK', @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop);
	        FETCH NEXT FROM ColCur INTO @ObjectID, @SchemaName, @ObjectName, @ObjSqlDefn, @ObjSqlDrop
        END
    CLOSE ColCur
    DEALLOCATE ColCur


    -- Generate script to Drop and Create the altered Type
    SET @ObjSqlDrop = Char(13) + Char(10) +
                      N'/********************************************************/' + Char(13) + Char(10) +
                      N'DROP TYPE ' + @UserType + Char(13) + Char(10)

    SET @ObjSqlDefn = Char(13) + Char(10) +
                      N'/********************************************************/' + Char(13) + Char(10) +
                      N'CREATE TYPE ' + @UserType + N' FROM ' + @NewType + N' NOT NULL' + Char(13) + Char(10)
                      
    INSERT #ObjectsScripts VALUES(N'UT', 0, N'dbo', @UserType, @ObjSqlDefn, @ObjSqlDrop);


    IF @WorkMode = N'SELECT' 
        SELECT * FROM #ObjectsScripts ORDER BY ObjIDX
    ELSE
    BEGIN
        IF @WorkMode = N'EXEC' PRINT N'Alter ' + @UserType + N'...'
        BEGIN TRY
            IF @WorkMode = N'EXEC' AND @UnderTran = 1
            Begin
                PRINT N'BEGIN TRAN...'
                BEGIN TRAN
            End
            
            IF @WorkMode = N'EXEC' PRINT N'Drop Objects...'
            DECLARE DropCur CURSOR LOCAL FAST_FORWARD FOR
                SELECT ObjectName, ObjSqlDrop FROM #ObjectsScripts ORDER BY ObjIDX

            OPEN DropCur
                FETCH NEXT FROM DropCur INTO @ObjectName, @ObjSqlDrop
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    BEGIN TRY
                        IF @WorkMode = N'EXEC' 
                            EXEC(@ObjSqlDrop) 
                        ELSE IF @WorkMode = N'PRINT'
                        Begin 
                            PRINT @ObjSqlDrop
                            PRINT N'GO'
                            PRINT N''
                        End
                    END TRY
                    BEGIN CATCH
                        SET @Err = @@ERROR
                        PRINT ERROR_MESSAGE()
                        PRINT N'Failed to Drop ' + @ObjectName + N'...' 
                        RAISERROR (N'GO OUT...', 16, 1)
                    END CATCH
                    FETCH NEXT FROM DropCur INTO @ObjectName, @ObjSqlDrop
                END
            CLOSE DropCur
            DEALLOCATE DropCur

            IF @WorkMode = N'EXEC' PRINT N'Restore Dropped Objects...'
            DECLARE RestCur CURSOR LOCAL FAST_FORWARD FOR
                SELECT ObjectName, ObjSqlDefn FROM #ObjectsScripts ORDER BY ObjIDX DESC

            OPEN RestCur
                FETCH NEXT FROM RestCur INTO @ObjectName, @ObjSqlDefn
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    BEGIN TRY
                        IF @WorkMode = N'EXEC' EXEC(@ObjSqlDefn) 
                        ELSE IF @WorkMode = N'PRINT'
                        Begin 
                            PRINT @ObjSqlDefn
                            PRINT N'GO'
                            PRINT N''
                        End
                    END TRY
                    BEGIN CATCH
                        SET @Err = @@ERROR
                        PRINT ERROR_MESSAGE()
                        PRINT N'Failed to Restore ' + @ObjectName + N'...' + Char(13) + Char(10) +
                              N'Probably there is a "@Table" with a field of type ' + @UserType
                        RAISERROR (N'GO OUT...', 16, 1)
                    END CATCH
                    FETCH NEXT FROM RestCur INTO @ObjectName, @ObjSqlDefn
                END
            CLOSE RestCur
            DEALLOCATE RestCur
                
            IF @WorkMode = N'EXEC' AND @UnderTran = 1 
            Begin
                PRINT N'COMMIT TRAN...'
                COMMIT TRAN
            End
            IF @WorkMode = N'EXEC' PRINT N'DONE'
        END TRY
        BEGIN CATCH
            PRINT ERROR_MESSAGE()
            IF @WorkMode = N'EXEC' AND @UnderTran = 1 
            Begin
                PRINT N'ROLLBACK TRAN'
                ROLLBACK TRAN
            End
            SET @Err = -1
        END CATCH
    END
        
    -- Drop Temp Tables
    DROP TABLE #FldList
    DROP TABLE #ObjectsScripts
     
    RETURN @Err
END
GO

Tutte le Utility