How to list nullable bit columns with no default bound with sp_bindefault

I generally create bit columns as NOT NULL DEFAULT dbo.BIT_NO, which in turn is a SQL server default which translates to 0. Today, I found out somebody on my team had created lots of bit fields on several tables as NULL with no default set. So I created the SQL statement below, to list these fields and make my life a bit easier. Comments are welcome. The fields I have to take care of is the ones with '*' on DNF column.

SELECT CASE WHEN dbo.syscomments.text IS NULL THEN '*' ELSE '' END AS [DNF], -- DNF = Default not found
       dbo.sysobjects.name AS tableName,
       dbo.syscolumns.name AS columnName,
       dbo.systypes.name AS typeName,
       dbo.syscolumns.isnullable, 
       REPLACE(dbo.syscomments.text, CHAR(13) + CHAR(10), '\n') AS [default bound with sp_bindefault]
FROM dbo.syscolumns
 INNER JOIN
     dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
 INNER JOIN
     dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
 LEFT OUTER JOIN
     dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id AND
                        OBJECTPROPERTY(dbo.syscolumns.cdefault, 'IsConstraint') = 0
WHERE (dbo.sysobjects.type = 'U') AND
      (dbo.systypes.name = 'bit') AND
      (dbo.syscolumns.isnullable = 0)
ORDER BY dbo.sysobjects.name,
         dbo.syscolumns.name

No Comments