How to add a default value constraints to NOT NULL columns

Solved: How to add a default value constraints to NOT NULL columns

This is a rather specific problem that I encountered, however, the solution can be used for a lot of other problems. What I had to do: add default value constraints to a specific type of columns in the whole Microsoft SQL Server database.

First things first: How do I find all the columns in the database that have a NOT NULL constraint?

SELECT o.name AS tablename, c.name AS columnname
FROM sys.columns c JOIN sys.objects o
ON c.object_id = o.object_id
WHERE o.type = 'U'
AND c.is_nullable = 0

Now to the other part of my initial problem: How do I find columns that DON'T have a default value constraint? For this you can use the default_object_id column of the sys.columns table. It holds the id of the default value constraint. In case there is no default value defined the column value is 0. And here is the code addition:

SELECT o.name AS tablename, c.name AS columnname
FROM sys.columns c JOIN sys.objects o
ON c.object_id = o.object_id
WHERE o.type = 'U'
AND c.is_nullable = 0
AND c.default_object_id = 0

The result of this query is a list of all the not null columns in the database that don't have a default value defined. If some of these columns should have the same default value (as it was the case in my problem) you can generate the 'ALTER TABLE' queries to add them. Your query could be something like this:

SELECT 'ALTER TABLE ' + o.name + ' ADD DEFAULT "defaultvalue" FOR ' + c.name + ' ;'
FROM sys.columns c JOIN sys.objects o
ON c.object_id = o.object_id
WHERE o.type = 'U'
AND c.is_nullable = 0
AND c.default_object_id = 0
...some more detailed where clause...

This query will generate an ALTER TABLE statement for  each one of your columns. All you need to do is run the statements. It should look something like this:

ALTER TABLE tablename ADD DEFAULT defaultvalue FOR column1;
ALTER TABLE tablename ADD DEFAULT defaultvalue FOR column2;
ALTER TABLE tablename ADD DEFAULT defaultvalue FOR column3;