Nullable bit columns with no default constraint

My manager asked me to find all nullable bit columns with no default constraint.

Here is what I came up with in T-SQL (SQL Server):


SELECT DB_NAME() AS DatabaseName, 
schemas.name
+ '.'
+ tables.name AS TableName,
columns.Name AS ColumnName
FROM sys.columns
INNER JOIN sys.tables 
  ON columns.object_id = tables.object_id
INNER JOIN sys.schemas 
  ON tables.schema_id = schemas.schema_id
WHERE system_type_id = 104
AND is_nullable = 1
AND NOT EXISTS(
SELECT 'x'
FROM sys.default_constraints
WHERE parent_object_id = columns.object_id
AND parent_column_id = columns.column_id
)
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s