August 2, 2015 at 11:16 pm
Hi All,
I have 595 default constraints in my database. I can return a list of them using the following:
select * from sys.default_constraints
Is there a way I can return a list of just the ones where NULL is still allowed? I want to update all of the columns with a default value to not allow NULLs.
Many thanks, Marisa
August 2, 2015 at 11:50 pm
I think I’ve solved this myself by just using:
select * from INFORMATION_SCHEMA.columns
I can then filter on the Column Default to show the records that aren’t NULL, and then subsequently filter on IS Nullable = Yes.
August 3, 2015 at 1:45 pm
Use sys.columns rather than the information_schema views.
select *
from sys.columns c
where cast(objectpropertyex(c.object_id, 'BaseType') as varchar(2)) = 'u' and c.is_nullable = 1 and c.is_computed = 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply