Altering a column (null -> not null) without knowing the data type

Question

Is there a possibility to alter a column from "allows null" to "does not allow null" without knowledge of the actual data type of the column?

I think no, so I have made as the basic skeleton code for my stored procedure:

SELECT t.name,c.max_length FROM sys.types t 
LEFT JOIN sys.columns c ON(t.system_type_id = c.system_type_id) 
WHERE object_id=OBJECT_ID(@TableName) AND c.name=@FieldName;

and

EXEC('UPDATE ' + @TableName + ' SET ' + @FieldName + ' = ' + @DefaultValue + ' WHERE ' + @FieldName + ' IS NULL');
EXEC('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @FieldName + ' NOT NULL');

I guess now I only have to get the return values from the first query back into the second. I can't get my head around how to get the values into a variable and then access them again. Ideas?

Answer 1

Since the INFORMATION_SCHEMA has all required information and is part of a SQL standard, it might be better to use that in this case (however, SQL Server's ALTER TABLE ALTER COLUMN is non-standard anyway so it might not matter as much).

Either way, you should also be checking for whether there's character length and/or numeric precision being specified, and make sure you're altering the table in the correct schema (and not getting dbo.TableName instead of customschema.TableName). You could try something like this (I used INFORMATION_SCHEMA here but you could easily refactor this to use the sys.columns view):

DECLARE @retVal VARCHAR(500);
SELECT @retVal = 
    CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0
        THEN CONCAT(DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH ,')')
        WHEN CHARACTER_MAXIMUM_LENGTH = -1 AND DATA_TYPE <> 'xml'
        THEN CONCAT(DATA_TYPE, '(MAX)')
        WHEN DATA_TYPE IN ('numeric', 'decimal')
        THEN CONCAT(DATA_TYPE, '(', NUMERIC_PRECISION,',', NUMERIC_SCALE,')')
        ELSE DATA_TYPE
        END 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = @schemaName
    AND TABLE_NAME = @tableName
    AND COLUMN_NAME = @columnName

@retVal will now capture datatypes like int, varchar(100), varbinary(MAX), or decimal(10,2) correctly.

And then build up a dynamic SQL Query like this:

DECLARE @sql VARCHAR(MAX);
SET @sql = 'ALTER TABLE ' + @schemaName + '.' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @retVal + ' NOT NULL;'
EXEC(@sql);