SQL Server – Transact SQL – Identify nullable columns that have default values assigned

Background

Reviewing some queries and wanted to see if I can change a “left outer join with a corresponding is null” into a “not exist clause“.

But, was not so sure whether in some changes the column could be nullable.

 

Default values

Here is a code for identifying columns marked nullable, but assigned default values.

There are a couple of ways for dispensing or assigning default values.

And, those are:

  1. Numbers
    • Identity Values
      • Ascending values assigned by the system
  2. Date (smalldatetime, datetime )
    • Default
      • System Date
  3. UniqueIdentifier
    • Default
      • newid()
      • newsequentialid

 

Code

 



use master
go

if object_id('[dbo].[sp_IdentifyNullableColumnsThatHaveDefaultValuesAssigned]') is null
begin

	exec('create procedure [dbo].[sp_IdentifyNullableColumnsThatHaveDefaultValuesAssigned] as ')

end
go

alter procedure [dbo].[sp_IdentifyNullableColumnsThatHaveDefaultValuesAssigned]
as

	/*
		A) sys.identity_columns
			https://msdn.microsoft.com/en-us/library/ms187334.aspx

	*/
	; with cteColumnBase
	(
		  [schemaName] 
		, [object_id]
		, [objectName]
		, [columnName]
		, [columnType]
		, [contraintDefaultName]
		, [contraintDefaultDefinition]
	)
	as
	(

		select 
				  [schemaName] 
					= tblSS.[name]

				, [objectID]
					= tblSO.[object_id]

				, objectName
					= tblSO.[name]

				, columnName
					= tblSC.[name]
					
				, columnType
					= tblST.[name]

				, [contraintDefaultName]
					= tblSDC.[name]

				, [contraintDefaultDefinition]
					= tblSDC.[definition]

		from   sys.[schemas] tblSS

		inner join sys.[objects] tblSO

			on tblSS.[schema_id] = tblSO.[schema_id]

		inner join sys.[columns] tblSC

			on tblSO.[object_id] = tblSC.[object_id]

		inner join sys.[types] tblST

			on tblSC.[system_type_id] = tblST.[system_type_id]


		left outer join [sys].[default_constraints] tblSDC

			on  tblSC.[object_id] = tblSDC.[parent_object_id]
			and tblSC.column_id = tblSDC.[parent_column_id]
	 

		/* Object is a user table */
		where  tblSO.[type] = 'U'

		/* Column is nullable */
		and    tblSC.is_nullable = 1

	)

	select 

			  cteCB.[schemaName] 
			, cteCB.objectName
			, cteCB.columnName
			, cteCB.columnType
			, cteCB.[contraintDefaultName]
			, cteCB.[contraintDefaultDefinition]
			, [numberofRows]
				= sum(tblSP.[rows])

	from   cteColumnBase cteCB

	inner join [sys].[partitions] tblSP

		on  cteCB.[object_id] = tblSP.[object_id]
		and tblSP.index_id in (0,1)

	where
			(
				 ( [contraintDefaultName] is not null )
			)


	group by
			  cteCB.[schemaName] 
			, cteCB.objectName
			, cteCB.columnName
			, cteCB.columnType
			, cteCB.[contraintDefaultName]
			, cteCB.[contraintDefaultDefinition]

	
	order by
			  cteCB.[schemaName]
			, cteCB.[objectName]
			, cteCB.[columnName]

go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_IdentifyNullableColumnsThatHaveDefaultValuesAssigned]'
go

grant execute on [dbo].[sp_IdentifyNullableColumnsThatHaveDefaultValuesAssigned] to [public]
go


Output

Here is a sample output

listcolumns

 

Source Control

Github

Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

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