SQL Server - Create Alter Table Statements quickly

This is a follow-up to my post for listing all columns with a specific data type.

The goal of the query below is the quickly create all the needed Alter Table statements for the new user defined data type that I'm going to be replacing the old one with.

As stated previously, we are planning on upgrading an older SQL Server 2005 server to SQL Server 2008.  In the process we determined that a couple of the columns would need to have their User Defined Data Types updated.

Instead of manually coding the Alter statements, I spent a few minutes creating a select statement that would allow me to copy the results to another SQL window with all the code needed to make the change.

In the process; I like my code to look pretty, oh so pretty.  So I'm using a couple of replicate statements to pad out spaces for the code to be created.



-- Get the Maximum TableName Length for the specificed type I'm going to update
DECLARE @MaxTableName int
    SET @MaxTableName = (
                   
                            SELECT MAX(LEN(o.Name))

                            FROM sys.columns AS c
                            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
                            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
                            Where   t.name = 'Date'
                                and t.is_user_defined = 1
                                and o.Type = 'U'
                        )


-- Get the Maximum Column Name length for the type I'm going to update
DECLARE @maxColumnName int
    SET @MaxColumnName =
                        (
                            SELECT MAX(LEN(c.Name))

                            FROM sys.columns AS c
                            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
                            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
                            Where   t.name = 'Date'
                                and t.is_user_defined = 1
                                and o.Type = 'U'
                        )   


-- Now create the pretty Alter statements

-- Will just need to copy the results to a new sql window and

-- you will have all the code needed to fix your problem
            SELECT ( 'ALTER TABLE dbo.['
                            + RTRIM(LTRIM(o.Name))
                            + ']'
                            + REPLICATE (' ',@MaxTableName-LEN(o.Name))
                            + ' ALTER COLUMN ['
                            + RTRIM(LTRIM(c.Name) )
                            + ']'
                            + REPLICATE (' ',@MaxColumnName-LEN(c.Name))
                            + ' DateTime NULL'
                )

            FROM sys.columns AS c
            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
            Where   t.name = 'Date'
                and t.is_user_defined = 1
                and o.Type = 'U'
               
            ORDER BY c.OBJECT_ID



So using the above code I got the resulting output:

ALTER TABLE dbo.[Test1]  ALTER COLUMN [Date1]   DateTime NULL
ALTER TABLE dbo.[Test1]  ALTER COLUMN [Date20]  DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date300] DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date4]   DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date50]  DateTime NULL

SQL Server - List all columns with a specific data type

We are in the process of upgrading an old server from SQL Server 2005 to SQL Server 2008.  In the preliminary investigation stage we discovered that there  are a couple User Defined Data Types that are named "Date" and "Time".

As these are now system data types we needed to determine which tables and columns would be affected by the upgrade.

Come to find out, this was a pretty simple task.  Using the system tables; Objects, Columns and Types we were able to pull a list together very quickly of the tables and columns that needed to be updated:

-- Select all Columns which have the user defined type name of 'Date'
SELECT
         SCHEMA_NAME(t.schema_id) AS SchemaName
        ,o.Name                   AS TableName
        ,o.Type_Desc              AS TableDescription
        ,o.Type                   AS TableType
        ,c.Object_id              AS ColumnObjectID 
        ,c.name                   AS ColumnName
        ,c.User_Type_ID
        ,t.name                   AS TypeName
        ,t.is_user_defined
    FROM     sys.columns AS c
        JOIN sys.types   AS t ON c.user_type_id = t.user_type_id
        JOIN sys.objects AS o ON c.Object_id    = o.Object_ID
    Where   t.name            = 'Date'  -- Type Name here
        and t.is_user_defined = 1       -- Yes, we want User Data Types only
        and o.Type            = 'U'     -- User Table Types only
   
ORDER BY o.name,c.name;
   

Well, we had a gotcha here...  The system not only saves which tables and columns that link to the UDT but all Indexes, Statistics, Stored Procedures, functions and views point to the UDT.  To see all affected types just comment out the last AND statement in the above script.

What we ended up having to do was

• Create new UDT
• You can not just change a UDT, it has to be created and old one dropped
• Also means new UDT can not have same name, of course you could get creative here and add a couple of steps to the process so as to retain the same name.
• Dropping the Statistics
• Disabling and in some cases having to drop the individual indexes involved
• Then Altering the table
• Rebuilding or recreating the indexes
• Recreate the statistics
• Recompile the Stored Procedures and UDF's
• Recreate any affected Views
• Then you can drop the old UDT

It was strange, even though they still point to the 'date' UDT the functions still worked.


Seems to me, that the the UDT's aren't accomplishing their main objective and that is making it easier to maintain consistency across tables.


For example:

You have a Social Security Number of 11 digits (9 digits plus the two dashes).  You created a UDT for SSN of char(11).  This would allow you to have any tables with a SSN in it to be marked with this UDT and make sure that all are a consistent 11 digits.

Now, the government decides that they need to add a couple more digits to the mess.  So we are tasked with finding and updating all SSN to this new length format.  The UDT makes this very simple to locate all affected columns.

But, updating this column isn't as simple as changing the UDT.  Instead, you have to some major changes through out the database(s).