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 - Altering tables and renaming columns

 A quick post on how to alter the data types and nullable attributes for a table.

In addition, since you would think that the same command statement would also take care of renaming a column I've added that as well.

First we create a test table to work with, as usual I'll stick with cars.

 After the table has been created, we realized that the Make is only five characters.  That will need to be fixed.

For some reason, it was decided that the Year can not be nullable.  They don't care about the Make or Model, go figure.

Then finally, we are told that Year really shouldn't be used.  They want us to use ModelYear instead.  But wait, ALTER TABLE just doesn't allow column name changes.  So we must use the system stored procedure SP_RENAME.

Below we'll be using SP_RENAME to rename the column.  But it can also be used to rename databases, indexes, objects and user data types.  See: msdn:SP_RENAME for more information.

 
-- Below creates a sample table called cars:

--- Create a Table ---
CREATE TABLE dbo.Cars
(
    ID     INT          Identity(1,1) NOT NULL
  , Year   INT         NULL
  , Make   VarChar(05) NULL
  , Model  VarChar(50) NULL
)

GO

-- the Alter TABLE statement allows you to modify the
-- data type and the nullable attribute of a column.


-- Here we are increasing the size of the Make
-- column to 50 characters from the initially created 05
ALTER TABLE dbo.Cars
    ALTER COLUMN Make Varchar(50) NULL
   
 
GO    

-- Let's prevent null values from being entered
-- into the Year column. 
--Note: If there are already null values in the
--Year column, this command would fail.
ALTER TABLE dbo.Cars
    ALTER COLUMN Year INT NOT NULL

GO

-- There is no ALTER command to rename a column
-- for this you will need to use 'sp_rename'
EXEC sp_rename 'dbo.Cars.Year', 'ModelYear', 'column'


--Note: You will receive a caution message indicating that
--      your change could break any existing scripts or
--      stored procedures.  So make sure you update any
--      affected stored procedures or scripts.