Query below summarizes default constraints in a database listing all distinct default value definitions for columns with the number of their occurrence.

Query

SELECT  COL.DefaultValue,
        COUNT(DISTINCT COL.DatabaseName || '.' || COL.TableName) AS Tables,
        COUNT(*) AS Columns
FROM    DBC.ColumnsV COL
JOIN    DBC.Tablesv TAB
ON      TAB.DatabaseName = COL.DatabaseName
AND     TAB.TableName = COL.TableName
AND     TAB.TableKind = 'T'
WHERE   COL.DataBaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
GROUP BY    COL.DefaultValue
ORDER BY    COUNT(*) DESC;

Columns

• DefaultValue - default value and NULL for columns without a default value
• Tables - number of tables with this default value (or number of tables with columns with no default for NULL row)
• Columns - number of columns with this default value

Rows

• One row represents one unique definition of default value
• Scope of rows: all distinct definitions of default column values in a database and one NULL row for columns without constraints
• Ordered by the number of occurrences in columns from the most popular

Sample results