MSSQL: alter column not null, in general

Question

In Microsoft SQL Server 2008 R2, I would like to change a nullable column to not null. Obviously, I could do this by restating the datatype, as

alter table t alter column c int not null

if the column t.c is of int datatype, for example. But what about in general, without restating the existing data type? I'm looking for some equivalent of

alter table t alter column c not null

where the existing datatype is kept in place, and only the nullability is turned off.

Background

I have done an audit of my database and found many cases where a column is specified as nullable but no null values occur in practice. I'd like to tighten up the schema to forbid null values in these columns. Manually writing the DDL to 'alter column' for each one is error-prone because I might get the datatype wrong. I could automatically generate the code by using a schema-dumper program which outputs the existing datatype of each column, but that too has risks, if the dumper program is not aware of the latest datatypes and outputs something else (as an example, suppose it doesn't know about datetime2 and writes out datetime instead).

The SQL server already knows what the column type is, so surely there is a way to tell it to keep that and just turn off the nullability.

If there's really no way to do it except by finding the existing datatype to put it into the DDL, perhaps you could recommend a suitable tool to use? I know about dbschema.pl from Sybase days but there might be something more modern, or some well-known fragment of SQL that prints out the necessary statements from the schema views.

Answer 1

Two approaches:

1) Expand on this answer to include consideration for max_length,precision,scale, and collation_name. If you have multiple schemas, you will need to accommodate that too.

SELECT
  'ALTER TABLE '
    +QUOTENAME(aud.[table_name])
    +' ALTER COLUMN '
    +QUOTENAME(aud.[column_name])
    +TYPE_NAME([system_type_id])
    +' NOT NULL;'
FROM MyColumnAuditList aud
INNER JOIN sys.columns col ON (
  col.[object_id] = OBJECT_ID(aud.[table_name]) AND
  col.[name] = aud.[column_name]
)

2) In SSMS, right-click the Database and select 'Script Database As'. Use your text parsing tools of choice to extract the column definitions from the result.

Answer 2

The 'two approaches' answer suggested by Anon is helpful. The website's comment box doesn't allow enough text so I will post my final answer here.

The linked answer has special provision for user data types, which my database doesn't have, so I am using the type_name builtin instead. This query tries to reverse-engineer the type for each column:

select t.name,
       c.name,
       case
         when type_name(c.system_type_id) in (
             'int', 'real', 'float', 'date', 'time', 'datetime', 'datetime2',
             'tinyint', 'smallint', 'smalldatetime', 'bit', 'bigint', 'timestamp',
             'image'
           ) then type_name(c.system_type_id)
         else type_name(c.system_type_id) + '('
           + case
               when precision = 0 then convert(varchar(10), c.max_length)
               else convert(varchar(10), precision) + ', ' + convert(varchar(10), scale)
             end
           + ')'
         end as ty
from sys.tables t
join sys.columns c
  on t.object_id = c.object_id
where c.is_nullable = 1
and   c.is_computed = 0
and   t.schema_id = 1
order by t.name,
         c.name

Then you can take each row from this query and do a check that no nulls exist before running 'alter table'. I am doing something like the following:

select case when
  exists (select 0 from TABLE)
  and not exists (select 0 from TABLE tablesample (1000 rows) where COLUMN is null)
then 1 else 0 end

for each TABLE, COLUMN returned by the first query. If the second query returns 1 then you can probably make the 'alter table' change. I use tablesample above to stop this being too heavy on the database, since I plan to run the check regularly; if the size of the table as returned by sp_spaceused is less than 100 kilobytes then I omit the tablesample clause.

Or, if you feel brave you could just run all the 'alter table' statements and let them fail if the column does contain nulls.

Oddly, I don't have permissions on the database to right-click in Management Studio and 'script database as', although I can do it for individual objects.