Search

MSFT-MSO: Support ALTER TYPE by Narayan_Iyer

Closed
as Won't Fix Help for as Won't Fix

46
0
Sign in
 to vote
Type: Bug
ID: 319134
Opened: 12/21/2007 2:05:04 AM
Access Restriction: Public
1
Workaround(s)
14
User(s) can reproduce this bug
There is no way to ALTER a "type" other drop and recreate? - What if I create a type with VARCHAR(10) and use it in many tables and after that I need to increase the width of the type?


P.S. I did search in BOL for any documentation on this, but no luck. Please let me know if I'm missing something obvious here.
Details (expand)
Product Language
English

Version

SQL Server 2008 November CTP

Category

SQL Engine

Operating System

Win2003 Enterprise Server (SP2)
Operating System Language
US English
Steps to Reproduce
-- Lets create a simple TYPE

CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

-- And map that to a column

CREATE TABLE foo (ssnumber SSN);

-- WOW, there is no way I can ALTER the type to increase the width of the column, let me see if I can drop and re-create it.

DROP TYPE SSN;

-- obviously, I can't as the TYPE is currently refferred by an object.

Actual Results
No way to modify an user defined TYPE.
Expected Results
The main advantage I see of using type in my objects is that I can modify the width of the column(s) in a bunch of objects by just modifying JUST ONE type.

I'd expect ALTER TYPE is supported.

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by atverweij on 7/15/2011 at 1:58 AM
I posted the script that I use to alter an UDDT.
Hope that someone benefits from it.
Posted by colesillaman on 7/14/2011 at 1:12 PM
Alter Type - now that table type paramters have been added to stored procedures... the need for an alter type has gone beyond just udf column types etc.
Hey its 2011 requests have been coming in since 2007 and earlier...

So how about it.. ?? The closed and wont fix.. over a 5 year period.. is a little long.. and negates any arguments of complexity etc.

Posted by Microsoft on 3/10/2011 at 4:56 PM
Hello Narayan,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”.

Please consider using tool like Visual Studio or SQL Studio to refactor your types. They will handle the renames including reference inside module definitions.

--
Umachandar, SQL Programmability Team
Posted by fotis12 on 12/22/2010 at 5:39 AM
WE NEED alter type too, both for simple and for table value types as well
It's killing us having to drop every time we need to modify something every procedure , view, function, table in the chain of dependancies, only for to be able to drop the old and create a new type with the same name but with just a minor modification.
Posted by Eidolon on 2/2/2010 at 8:45 AM
As niyer pointed out, not being able to alter a type really defeats the main purpose of declaring a type, which is to have a consistent, "inheritable" type. You would expect that by creating a type, if in the future your data grows and you need to change the field from an INT to a BIGINT, or from VC(10) to VC(50) you should be able to alter the type and all the tables which use it would be updated automagically.
Posted by Pawel Potasinski on 12/14/2009 at 1:22 AM
If only we could perform drops and recreates within a single transaction... See another item for details: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=365876.
Posted by Patrick Stitts on 2/14/2008 at 2:30 PM
I suggested this back in SQL 7.0... been waiting ever since for this feature. It would be a very powerful addition for those of us who use user-defined types as an easy way to keep data types consistent throughout the database. I know there will be rules (like can't convert text to int, etc.) but most of the time you end up changing the actual length of a field instead of totally changing the data type.
Posted by Narayan_Iyer on 1/17/2008 at 5:06 AM
This defeats the usage of the 'TYPE' in my code. The main advantage I see of using TYPE is to modify the column width in several objects by modifying it in one place.

As per your workaround, if I have to drop the TYPE, first I need to drop all the objects that are referring to that TYPE. :-(

Anyways, I understand the busy schedule and I will let you take your own call on this.
Posted by Microsoft on 1/17/2008 at 12:50 AM
Thanks for the suggestion and feedback.

We're in the process of seriously triaging customer requests for the Katmai release. Given the time and resource constrain, we might not be able to address all customer DCRs for the release.

Meanwhile, as a workaround, please do stick with the drop and recreate workaround if you indeed need to change the alias type.

thanks-michael
Sign in to post a workaround.
Posted by atverweij on 7/15/2011 at 1:57 AM
-- ALTER USER DEFINED DATATYPE

-- USE [CORRECTDATABASE] !!!!!

-- RUN WITH RESULTS TO TEXT!!!


--!!!!!!!!!!!     FIRST TEST ON A COPY DATABASE     !!!!!!!!
--!!!!!!!!!!! FIRST MAKE A BACKUP FROM YOUR DATABASE !!!!!!!!
--!!!!!!!!!!! MAKE SURE YOU HAVE PLENTY OF LOG SPACE !!!!!!!!
SET NOCOUNT ON
DECLARE @UDT VARCHAR(100)
DECLARE @NEW_DATATYPE VARCHAR(100)

SET @UDT = 'MyUddt' --the name of the UDDT
SET @NEW_DATATYPE = 'char(2)' --the new datatype for the UDDT

BEGIN TRANSACTION
DECLARE @STMT VARCHAR(MAX)
DECLARE @NAME VARCHAR(MAX)
DECLARE @LASTNAME VARCHAR(MAX)
DECLARE @COLNAME VARCHAR(MAX)
DECLARE @CURSTMT VARCHAR(MAX)
DECLARE @COLUMN_ID INT
DECLARE @REFNAME VARCHAR(MAX)
DECLARE @REFCOLNAME VARCHAR(MAX)
DECLARE @REFCURSTMT VARCHAR(MAX)
DECLARE @POST VARCHAR(MAX)
DECLARE @CURRENTPOST VARCHAR(MAX)
-- SAVE ORIGINAL TABLE INFORMATION
SELECT 'SAVE ORIGINAL TABLE INFORMATION'
SELECT
    *
INTO
    #TMP_TABLE_INFORMATION
FROM
    INFORMATION_SCHEMA.COLUMNS i inner join sys.tables t --skip views
        on i.table_name = t.name
WHERE
    i.DOMAIN_NAME = @UDT
-- END SAVE ORIGINAL TABLE INFORMATION

--SAVE FK CONSTRAINTS
SELECT 'SAVE FK CONSTRAINTS'
CREATE TABLE #TMP_FKS(CREATE_OBJECT VARCHAR(MAX))

SET @CURSTMT = NULL
SET @REFCURSTMT = NULL
SET @LASTNAME = ''
DECLARE CREATE_TEXTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'ALTER TABLE [' + O.NAME + '] ADD CONSTRAINT [' + A.NAME + '] FOREIGN KEY (',
    ' REFERENCES [' + RO.NAME + '](',
    '[' + C.NAME + ']',
    '[' + RC.NAME + ']'
FROM
    SYS.FOREIGN_KEYS A INNER JOIN SYS.OBJECTS O
        ON A.PARENT_OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.FOREIGN_KEY_COLUMNS I
        ON A.OBJECT_ID = I.CONSTRAINT_OBJECT_ID
        AND A.PARENT_OBJECT_ID = I.PARENT_OBJECT_ID
    INNER JOIN SYS.COLUMNS C
        ON I.PARENT_OBJECT_ID = C.OBJECT_ID
        AND I.PARENT_COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.OBJECTS RO
        ON A.REFERENCED_OBJECT_ID = RO.OBJECT_ID
    INNER JOIN SYS.COLUMNS RC
        ON I.REFERENCED_OBJECT_ID = RC.OBJECT_ID
        AND I.REFERENCED_COLUMN_ID = RC.COLUMN_ID
ORDER BY
    A.NAME,
    I.CONSTRAINT_COLUMN_ID
    
OPEN CREATE_TEXTS
FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @REFNAME, @COLNAME, @REFCOLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NAME <> @LASTNAME
    BEGIN
        IF @CURSTMT IS NOT NULL
        BEGIN
            INSERT INTO #TMP_FKS(CREATE_OBJECT) VALUES (@CURSTMT + ')' + @REFCURSTMT + ')')
        END
        SET @CURSTMT = @NAME
        SET @REFCURSTMT = @REFNAME
        SET @LASTNAME = @NAME
    END
    IF @CURSTMT <> @NAME
    BEGIN
        SET @CURSTMT = @CURSTMT + ','
        SET @REFCURSTMT = @REFCURSTMT + ','
    END
    SET @CURSTMT = @CURSTMT + @COLNAME
    SET @REFCURSTMT = @REFCURSTMT + @REFCOLNAME
    FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @REFNAME, @COLNAME, @REFCOLNAME
END
IF @CURSTMT IS NOT NULL
BEGIN
    INSERT INTO #TMP_FKS(CREATE_OBJECT) VALUES (@CURSTMT + ')' + @REFCURSTMT + ')')
END
CLOSE CREATE_TEXTS
DEALLOCATE CREATE_TEXTS
-- END SAVE FK CONSTRAINTS

-- REMOVE FK CONSTRAINTS
SELECT 'REMOVE FK CONSTRAINTS'
DECLARE CREATE_TEXTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'ALTER TABLE [' + O.NAME + '] DROP CONSTRAINT [' + A.NAME + ']'
FROM
    SYS.FOREIGN_KEYS A INNER JOIN SYS.OBJECTS O
        ON A.PARENT_OBJECT_ID = O.OBJECT_ID
ORDER BY
    A.NAME
        
OPEN CREATE_TEXTS
FETCH NEXT FROM CREATE_TEXTS INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@NAME)
    FETCH NEXT FROM CREATE_TEXTS INTO @NAME
END
CLOSE CREATE_TEXTS
DEALLOCATE CREATE_TEXTS
-- END REMOVE FK CONSTRAINTS

-- SAVE PK CONSTRAINTS WITH THE UDT
SELECT 'SAVE PK CONSTRAINTS WITH THE UDT'
CREATE TABLE #TMP_PKS(CREATE_OBJECT VARCHAR(MAX))

SET @CURSTMT = NULL
SET @LASTNAME = ''

DECLARE CREATE_PKS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'ALTER TABLE ' + O.NAME + ' ADD CONSTRAINT [' + A.NAME + '] PRIMARY KEY ' + CASE A.TYPE WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + '(',
    '[' + C.NAME + ']' + CASE IS_DESCENDING_KEY WHEN 1 THEN ' DESC' ELSE '' END,
    I.INDEX_COLUMN_ID
FROM
    SYS.INDEXES A INNER JOIN SYS.OBJECTS O
        ON A.OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
WHERE
    A.IS_PRIMARY_KEY = 1
AND A.TYPE > 0
AND A.NAME IN (
    SELECT A.NAME FROM SYS.INDEXES A INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.USER_TYPE_ID = T.USER_TYPE_ID
    WHERE
        T. NAME = @UDT
)
ORDER BY
    A.NAME,
    I.INDEX_COLUMN_ID
    
OPEN CREATE_PKS
FETCH NEXT FROM CREATE_PKS INTO @NAME, @COLNAME, @COLUMN_ID
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NAME <> @LASTNAME
    BEGIN
        IF @CURSTMT IS NOT NULL
        BEGIN
            INSERT INTO #TMP_PKS(CREATE_OBJECT) VALUES (@CURSTMT + ')')
        END
        SET @CURSTMT = @NAME
        SET @LASTNAME = @NAME
    END
    IF @COLUMN_ID > 1
    BEGIN
        SET @CURSTMT = @CURSTMT + ','
    END
    SET @CURSTMT = @CURSTMT + @COLNAME
    FETCH NEXT FROM CREATE_PKS INTO @NAME, @COLNAME, @COLUMN_ID
END
IF @CURSTMT IS NOT NULL
BEGIN
    INSERT INTO #TMP_PKS(CREATE_OBJECT) VALUES (@CURSTMT + ')')
END
CLOSE CREATE_PKS
DEALLOCATE CREATE_PKS
-- END SAVE PK CONSTRAINTS WITH THE UDT

-- REMOVE PK CONSTRAINTS WITH THE UDT
SELECT 'REMOVE PK CONSTRAINTS WITH THE UDT'
DECLARE REMOVE_PK CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT
    'ALTER TABLE [' + O.NAME + '] DROP CONSTRAINT [' + A.NAME + ']'
FROM
    SYS.INDEXES A INNER JOIN SYS.OBJECTS O
        ON A.OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.USER_TYPE_ID = T.USER_TYPE_ID
WHERE
    A.IS_PRIMARY_KEY = 1
AND A.TYPE > 0
AND A.NAME IN (
    SELECT A.NAME FROM SYS.INDEXES A INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.USER_TYPE_ID = T.USER_TYPE_ID
    WHERE
        T. NAME = @UDT
)

OPEN REMOVE_PK
FETCH NEXT FROM REMOVE_PK INTO @STMT

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM REMOVE_PK INTO @STMT
END
CLOSE REMOVE_PK
DEALLOCATE REMOVE_PK
-- END REMOVE PK CONSTRAINTS WITH THE UDT

-- SAVE INDEXES WITH THE UDT
SELECT 'SAVE INDEXES WITH THE UDT'
CREATE TABLE #TMP_INDEXES(CREATE_OBJECT VARCHAR(MAX))

DECLARE @INCLUDED BIT
DECLARE @LASTINCLUDED BIT

SET @CURSTMT = NULL
SET @LASTNAME = ''

DECLARE CREATE_INDEXES CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'CREATE ' + CASE A.IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE '' END + CASE A.TYPE WHEN 1 THEN 'CLUSTERED' ELSE '' END + ' INDEX [' + A.NAME + '] ON [' + O.NAME + '](',
    '[' + C.NAME + ']' + CASE IS_DESCENDING_KEY WHEN 1 THEN ' DESC' ELSE '' END,
    I.INDEX_COLUMN_ID,
    I.IS_INCLUDED_COLUMN
FROM
    SYS.INDEXES A INNER JOIN SYS.OBJECTS O
        ON A.OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
WHERE
    A.IS_PRIMARY_KEY = 0
AND A.IS_UNIQUE_CONSTRAINT = 0
AND A.TYPE > 0
AND A.NAME IN (
    SELECT A.NAME FROM SYS.INDEXES A INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
    WHERE
        T. NAME = @UDT
)
ORDER BY
    A.NAME,
    I.INDEX_COLUMN_ID
        
OPEN CREATE_INDEXES
FETCH NEXT FROM CREATE_INDEXES INTO @NAME, @COLNAME, @COLUMN_ID, @INCLUDED
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NAME <> @LASTNAME
    BEGIN
        IF @CURSTMT IS NOT NULL
        BEGIN
            INSERT INTO #TMP_INDEXES(CREATE_OBJECT) VALUES (@CURSTMT + ')')
        END
        SET @CURSTMT = @NAME
        SET @LASTNAME = @NAME
        SET @LASTINCLUDED = 0
    END
    IF @INCLUDED = 1
    BEGIN
        IF @LASTINCLUDED = 0
        BEGIN
            SET @LASTINCLUDED = 1
            SET @CURSTMT = @CURSTMT + ') INCLUDES ('
            SET @COLUMN_ID = 0
        END
    END
    IF @COLUMN_ID > 1
    BEGIN
        SET @CURSTMT = @CURSTMT + ','
    END
    SET @CURSTMT = @CURSTMT + @COLNAME
    FETCH NEXT FROM CREATE_INDEXES INTO @NAME, @COLNAME, @COLUMN_ID, @INCLUDED
END
IF @CURSTMT IS NOT NULL
BEGIN
    INSERT INTO #TMP_INDEXES(CREATE_OBJECT) VALUES (@CURSTMT + ')')
END
CLOSE CREATE_INDEXES
DEALLOCATE CREATE_INDEXES
-- END SAVE INDEXES WITH THE UDT

-- REMOVE INDEXES WITH THE UDT
SELECT 'REMOVE INDEXES WITH THE UDT'
DECLARE REMOVE_INDEX CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT
    'DROP INDEX [' + A.NAME + '] ON [' + O.NAME + ']'
FROM
    SYS.INDEXES A INNER JOIN SYS.OBJECTS O
        ON A.OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
WHERE
    A.IS_PRIMARY_KEY = 0
AND A.IS_UNIQUE_CONSTRAINT = 0
AND A.TYPE > 0
AND A.NAME IN (
    SELECT A.NAME FROM SYS.INDEXES A INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
    WHERE
        T. NAME = @UDT
)

OPEN REMOVE_INDEX
FETCH NEXT FROM REMOVE_INDEX INTO @STMT

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM REMOVE_INDEX INTO @STMT
END
CLOSE REMOVE_INDEX
DEALLOCATE REMOVE_INDEX
-- END REMOVE INDEXES WITH THE UDT

-- SAVE UNIQUE CONSTRAINTS WITH THE UDT
SELECT 'SAVE UNIQUE CONSTRAINTS WITH THE UDT'
CREATE TABLE #TMP_CONSTRAINTS(CREATE_OBJECT VARCHAR(MAX))

SET @CURSTMT = NULL
SET @LASTNAME = ''

DECLARE CREATE_TEXTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'ALTER TABLE [' + O.NAME + '] ADD CONSTRAINT [' + A.NAME + '] UNIQUE ' + CASE A.TYPE WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (',
    '[' + C.NAME + ']' + CASE IS_DESCENDING_KEY WHEN 1 THEN ' DESC' ELSE '' END,
    I.INDEX_COLUMN_ID
FROM
    SYS.INDEXES A INNER JOIN SYS.OBJECTS O
        ON A.OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
WHERE
    A.IS_PRIMARY_KEY = 0
AND A.IS_UNIQUE_CONSTRAINT = 1
AND A.TYPE > 0
AND A.NAME IN (
    SELECT A.NAME FROM SYS.INDEXES A INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
    WHERE
        T. NAME = @UDT
)
ORDER BY
    A.NAME,
    I.INDEX_COLUMN_ID
        
OPEN CREATE_TEXTS
FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @COLNAME, @COLUMN_ID
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NAME <> @LASTNAME
    BEGIN
        IF @CURSTMT IS NOT NULL
        BEGIN
            INSERT INTO #TMP_CONSTRAINTS(CREATE_OBJECT) VALUES (@CURSTMT + ')' )
        END
        SET @CURSTMT = @NAME
        SET @LASTNAME = @NAME
    END        
    IF @CURSTMT <> @NAME
    BEGIN
        SET @CURSTMT = @CURSTMT + ','
    END
    SET @CURSTMT = @CURSTMT + @COLNAME
    FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @COLNAME, @COLUMN_ID
END
IF @CURSTMT IS NOT NULL
BEGIN
    INSERT INTO #TMP_CONSTRAINTS(CREATE_OBJECT) VALUES (@CURSTMT + ')' )
END
CLOSE CREATE_TEXTS
DEALLOCATE CREATE_TEXTS
-- END SAVE UNIQUE CONSTRAINTS WITH THE UDT

-- REMOVE UNIQUE CONSTRAINTS WITH THE UDT
SELECT 'REMOVE UNIQUE CONSTRAINTS WITH THE UDT'
DECLARE REMOVE_CONSTRAINT CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT
    'ALTER TABLE [' + O.NAME + '] DROP CONSTRAINT [' + A.NAME + ']'
FROM
    SYS.INDEXES A INNER JOIN SYS.OBJECTS O
        ON A.OBJECT_ID = O.OBJECT_ID
    INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
WHERE
    A.IS_PRIMARY_KEY = 0
AND A.IS_UNIQUE_CONSTRAINT = 1
AND A.TYPE > 0
AND A.NAME IN (
    SELECT A.NAME FROM SYS.INDEXES A INNER JOIN    SYS.INDEX_COLUMNS I
        ON A.OBJECT_ID = I.OBJECT_ID
        AND A.INDEX_ID = I.INDEX_ID
    INNER JOIN SYS.COLUMNS C
        ON I.OBJECT_ID = C.OBJECT_ID
        AND I.COLUMN_ID = C.COLUMN_ID
    INNER JOIN SYS.TYPES T
        ON C.user_type_id = T.user_type_id
    WHERE
        T. NAME = @UDT
)

OPEN REMOVE_CONSTRAINT
FETCH NEXT FROM REMOVE_CONSTRAINT INTO @STMT

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM REMOVE_CONSTRAINT INTO @STMT
END
CLOSE REMOVE_CONSTRAINT
DEALLOCATE REMOVE_CONSTRAINT
-- END REMOVE UNIQUE CONSTRAINTS WITH THE UDT

-- TABLES: CONVERT THE UDT BACK TO THE ORIGINAL DATATYPE
SELECT 'TABLES: CONVERT THE UDT BACK TO THE ORIGINAL DATATYPE'

DECLARE RESTORE_TABLES CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' +
    'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
    CASE WHEN DATA_TYPE LIKE('%CHAR') THEN '(' + LTRIM(CHARACTER_MAXIMUM_LENGTH) + ')'
         WHEN DATA_TYPE IN('NUMERIC','DECIMAL') THEN '(' + LTRIM(NUMERIC_PRECISION) + ',' + LTRIM(NUMERIC_SCALE) + ')'
         ELSE '' END +
    CASE IS_NULLABLE
         WHEN 'NO' THEN ' NOT NULL'
         ELSE ' NULL' END
FROM
    #TMP_TABLE_INFORMATION
WHERE
    DOMAIN_NAME = @UDT

OPEN RESTORE_TABLES
FETCH NEXT FROM RESTORE_TABLES INTO @STMT

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM RESTORE_TABLES INTO @STMT
END
CLOSE RESTORE_TABLES
DEALLOCATE RESTORE_TABLES
-- END TABLES: CONVERT TO ORIGINAL DATATYPE


-- SAVE TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES
SELECT 'SAVE TRIGGERS, USER DEFINED FUNCTIONS, STORED PROCEDURES'
CREATE TABLE #TMP_OBJECTS(CREATE_OBJECT VARCHAR(MAX))

SET @CURSTMT = NULL
SET @LASTNAME = ''
DECLARE CREATE_TEXTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    A.NAME + A.XTYPE,
    B.TEXT AS CREATE_OBJECT
FROM
    SYSOBJECTS A INNER JOIN SYSCOMMENTS B
        ON A.ID=B.ID
WHERE
    XTYPE IN ('TR', 'P', 'FN', 'TF')
AND A.NAME + A.XTYPE IN (
    SELECT
        A.NAME + A.XTYPE
    FROM
        SYSOBJECTS A INNER JOIN SYSCOMMENTS B
            ON A.ID=B.ID
    WHERE
        XTYPE IN ('TR', 'P', 'FN', 'TF')
    AND CHARINDEX(@UDT, B.TEXT, 1) > 0
)
ORDER BY
    A.NAME + A.XTYPE,
    B.COLID
    
OPEN CREATE_TEXTS
FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NAME <> @LASTNAME
    BEGIN
        IF @CURSTMT IS NOT NULL
        BEGIN
            INSERT INTO #TMP_OBJECTS(CREATE_OBJECT) VALUES (@CURSTMT)
        END
        SET @CURSTMT = @STMT
        SET @LASTNAME = @NAME
    END
    ELSE
    BEGIN
        SET @CURSTMT = @CURSTMT + @STMT
    END
    FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @STMT
END
IF @CURSTMT IS NOT NULL
BEGIN
    INSERT INTO #TMP_OBJECTS(CREATE_OBJECT) VALUES (@CURSTMT)
END
CLOSE CREATE_TEXTS
DEALLOCATE CREATE_TEXTS
-- END SAVE TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES

-- DROP TRIGGERS, USER DEFINED FUNCTIONS, STORED PROCEDURES
SELECT 'DROP TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES'
DECLARE DROP_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT
    CASE A.XTYPE
        WHEN 'TR' THEN 'DROP TRIGGER [' + A.NAME + ']'
        WHEN 'P' THEN 'DROP PROCEDURE [' + S.NAME + '].[' + A.NAME + ']'
        ELSE 'DROP FUNCTION [' + S.NAME + '].[' + A.NAME + ']'
    END
FROM
    SYSOBJECTS A INNER JOIN SYSCOMMENTS B
        ON A.ID=B.ID
    LEFT OUTER JOIN SYS.OBJECTS T
        ON A.ID = T.OBJECT_ID
    LEFT OUTER JOIN SYS.SCHEMAS S
        ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE
    A.XTYPE IN ('TR', 'P', 'FN', 'TF')
AND A.NAME + A.XTYPE IN (
    SELECT
        A.NAME + A.XTYPE
    FROM
        SYSOBJECTS A INNER JOIN SYSCOMMENTS B
            ON A.ID=B.ID
    WHERE
        XTYPE IN ('TR', 'P', 'FN', 'TF')
    AND CHARINDEX(@UDT, B.TEXT, 1) > 0
)

OPEN DROP_OBJECTS
FETCH NEXT FROM DROP_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM DROP_OBJECTS INTO @STMT
END
CLOSE DROP_OBJECTS
DEALLOCATE DROP_OBJECTS
-- END DROP TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES

-- SAVE VIEWS
SELECT 'SAVE VIEWS'
CREATE TABLE #TMP_VIEWS(CREATE_OBJECT VARCHAR(MAX))

SET @CURSTMT = NULL
SET @LASTNAME = ''
DECLARE CREATE_TEXTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    A.NAME + A.XTYPE,
    B.TEXT AS CREATE_OBJECT
FROM
    SYSOBJECTS A INNER JOIN SYSCOMMENTS B
        ON A.ID=B.ID
WHERE
    XTYPE = 'V'
ORDER BY
    A.NAME + A.XTYPE,
    B.COLID
    
OPEN CREATE_TEXTS
FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @NAME <> @LASTNAME
    BEGIN
        IF @CURSTMT IS NOT NULL
        BEGIN
            INSERT INTO #TMP_VIEWS(CREATE_OBJECT) VALUES (@CURSTMT)
        END
        SET @CURSTMT = @STMT
        SET @LASTNAME = @NAME
    END
    ELSE
    BEGIN
        SET @CURSTMT = @CURSTMT + @STMT
    END
    FETCH NEXT FROM CREATE_TEXTS INTO @NAME, @STMT
END
IF @CURSTMT IS NOT NULL
BEGIN
    INSERT INTO #TMP_VIEWS(CREATE_OBJECT) VALUES (@CURSTMT)
END
CLOSE CREATE_TEXTS
DEALLOCATE CREATE_TEXTS
-- END SAVE VIEWS

-- DROP VIEWS
SELECT 'DROP VIEWS'
DECLARE DROP_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT
    ' DROP VIEW [' + S.NAME + '].[' + A.NAME + ']'
FROM
    SYSOBJECTS A INNER JOIN SYSCOMMENTS B
        ON A.ID=B.ID
    LEFT OUTER JOIN SYS.OBJECTS T
        ON A.ID = T.OBJECT_ID
    LEFT OUTER JOIN SYS.SCHEMAS S
        ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE
    A.XTYPE = 'V'

OPEN DROP_OBJECTS
FETCH NEXT FROM DROP_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM DROP_OBJECTS INTO @STMT
END
CLOSE DROP_OBJECTS
DEALLOCATE DROP_OBJECTS
-- END DROP VIEWS

-- ALTER USER DEFINED DATATYPE
SELECT 'ALTER USER DEFINED DATATYPE'
SET @STMT = 'DROP TYPE ' + @UDT
EXEC(@STMT)
SET @STMT = 'CREATE TYPE ' + @UDT + ' FROM ' + @NEW_DATATYPE
EXEC(@STMT)
-- END ALTER USER DEFINED DATATYPE

-- TABLES: CONVERT THE ORIGINAL DATATYPE BACK TO THE UDT
SELECT 'TABLES: CONVERT THE ORIGINAL DATATYPE BACK TO THE UDT'
DECLARE RESTORE_TABLES CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' +
    'ALTER COLUMN ' + COLUMN_NAME + ' ' + @UDT +
    CASE IS_NULLABLE
         WHEN 'NO' THEN ' NOT NULL'
         ELSE ' NULL' END
FROM
    #TMP_TABLE_INFORMATION
WHERE
    DOMAIN_NAME = @UDT

OPEN RESTORE_TABLES
FETCH NEXT FROM RESTORE_TABLES INTO @STMT

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM RESTORE_TABLES INTO @STMT
END
CLOSE RESTORE_TABLES
DEALLOCATE RESTORE_TABLES
-- END TABLES: CONVERT THE ORIGINAL DATATYPE BACK TO THE UDT

-- RESTORE PK CONSTRAINTS WITH THE UDT
SELECT 'RESTORE PK CONSTRAINTS WITH THE UDT'
DECLARE CREATE_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    CREATE_OBJECT
FROM
    #TMP_PKS

OPEN CREATE_OBJECTS
FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
END
CLOSE CREATE_OBJECTS
DEALLOCATE CREATE_OBJECTS
-- END RESTORE PK CONSTRAINTS WITH THE UDT

-- RESTORE CONSTRAINTS WITH THE UDT
SELECT 'RESTORE CONSTRAINTS WITH THE UDT'
DECLARE CREATE_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    CREATE_OBJECT
FROM
    #TMP_CONSTRAINTS

OPEN CREATE_OBJECTS
FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
END
CLOSE CREATE_OBJECTS
DEALLOCATE CREATE_OBJECTS
-- END RESTORE CONSTRAINTS WITH THE UDT

-- RESTORE INDEXES WITH THE UDT
SELECT 'RESTORE INDEXES WITH THE UDT'
DECLARE CREATE_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    CREATE_OBJECT
FROM
    #TMP_INDEXES

OPEN CREATE_OBJECTS
FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
END
CLOSE CREATE_OBJECTS
DEALLOCATE CREATE_OBJECTS
-- END RESTORE INDEXES WITH THE UDT

-- RESTORE FK CONSTRAINTS WITH THE UDT
SELECT 'RESTORE FK CONSTRAINTS WITH THE UDT'
DECLARE CREATE_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    CREATE_OBJECT
FROM
    #TMP_FKS

OPEN CREATE_OBJECTS
FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
END
CLOSE CREATE_OBJECTS
DEALLOCATE CREATE_OBJECTS
-- END RESTORE FK CONSTRAINTS WITH THE UDT


--COMMIT POINT: OTHERWISE THE RECREATION OP THE STORED PROCEDURES, VIEWS , TRIGGERS AND UDF CAN GIVE A DEADLOCK
COMMIT TRANSACTION


-- RESTORE VIEWS
SELECT 'RESTORE VIEWS'
DECLARE CREATE_VIEWS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    CREATE_OBJECT
FROM
    #TMP_VIEWS

OPEN CREATE_VIEWS
FETCH NEXT FROM CREATE_VIEWS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@STMT)
    FETCH NEXT FROM CREATE_VIEWS INTO @STMT
END
CLOSE CREATE_VIEWS
DEALLOCATE CREATE_VIEWS
-- END RESTORE VIEWS

-- RESTORE TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES
SELECT 'RESTORE TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES'
DECLARE CREATE_OBJECTS CURSOR LOCAL FAST_FORWARD FOR
SELECT
    CREATE_OBJECT
FROM
    #TMP_OBJECTS

OPEN CREATE_OBJECTS
FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @STMT
    EXEC(@STMT)
    FETCH NEXT FROM CREATE_OBJECTS INTO @STMT
END
CLOSE CREATE_OBJECTS
DEALLOCATE CREATE_OBJECTS
-- END RESTORE TRIGGERS, USER DEFINED FUNCTIONS AND STORED PROCEDURES
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement