Friday 14 September 2012

Finding Default Column Constraints where the columns allow NULLs

Finding Default COlumn Constraints where the columns allow NULLs Investigating a database I wrote these to find some design inconsistencies.

I plan to make a version for foreign key constraints too. SQL 2000 script

SELECT	  
	  u.name AS OwnerName
	, tab.name AS TableName
	, col.name AS ColumnName
	, col.isnullable
	, con.name AS DefaultName 
	, com.text AS DefaultValue
FROM sysobjects tab
INNER JOIN sysusers u ON tab.uid = u.uid
INNER JOIN syscolumns col ON col.id = tab.id
INNER JOIN sysobjects con ON con.id = col.cdefault 
       AND con.xtype = 'D'
INNER JOIN syscomments com ON com.id = con.id 
 LEFT JOIN syscolumns dfc ON dfc.id = com.id
WHERE col.isnullable = 1
ORDER BY 1,2

SQL 2005+ script

SELECT 
	 Tab.name AS Tablename
	,Col.name AS Columnname
	,Col.is_nullable
	,Con.name AS DefaultName
	,[Definition] AS DefaultValue
FROM	sys.all_columns Col
INNER JOIN sys.tables Tab 
		ON Col.object_id = Tab.object_id
INNER JOIN sys.default_constraints Con
		ON Col.default_object_id = Con.object_id
WHERE col.is_nullable = 1
ORDER BY 1,2

Similar to the above, most can be done from INFORMATION_SCHEMA view ins sql 2005 (with the exception of the default name)

SELECT
	 TABLE_SCHEMA AS SchemaName
	,TABLE_NAME AS TableName
	,COLUMN_NAME AS ColumnName
	,IS_NULLABLE
	,COLUMN_DEFAULT AS DefaultValue
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT IS NOT NULL
  AND IS_NULLABLE = 'YES'
ORDER BY 1,2,3

No comments: