Transact SQL :- List Default Constraints

Background

List SQL Server Default Constraints.

Metadata

If one decides to use Sequences, one has to be able to document where it is being used.

Identity Columns

Identify Columns has self documenting tooling.

Inclusive :-

  1. sys.columns
    • is_identity
  2. sys.identity_columns
  3. OBJECTPROPERTY
    • TableHasIdentity
  4.  COLUMNPROPERTY
    • IsIdentity

Sequence

There is really nothing like a Sequence Column.

They are defined outside of a table definition.

It is not intrinsic so to speak.

It is a service that can be used as a communal toolset.

Outline

To detect use we can ask :-

  1. sys.columns
  2. sys.default_constraints
  3. sys.sql_expression_dependencies

Code


  set noexec off
 go

 /*
    exec sp_helpuser
 */
 if (
             ( SUSER_ID('rdsa') is not null )
        and  ( db_name() = 'master')
    )
 begin

    print 'We are on Amazon AWS/RDS and cannot create objects in the master database'
    print 'Please stop processing!'

    set noexec on
 
 end
 go
 
 
 if SUSER_ID('rdsa') is null
 begin

    use [master]
 
 end
 go
 

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

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

 end
 go

 alter procedure [dbo].[sp_helpConstraintDefault] 
 as
 begin
  
   select 

              [schema]
                = tblSS.[name]

            , [object] 
                = tblSO.[name]

            , [column] 
                = tblSC.[name]

            , [columnType]
                = tblST.[name]

            , [is_identity]
                = tblSC.is_identity

            , [is_rowguidcol]
                = tblSC.is_rowguidcol

            , [columnDefault]
                = tblSDC.[name]
   
            , [definition]
                = tblSDC.[definition]

            , [referencedObject]
                = quotename
                    (
                        tblSORefS.[name]
                    )
                    + '.'
                    + quoteName
                        (
                            tblSORef.[name]
                        )

            , [referencedObjectType]
                = tblSORef.[type_desc]

    from   sys.columns tblSC

    inner join sys.types tblST
            
        on  tblSC.system_type_id = tblST.system_type_id

        and tblSC.user_type_id = tblST.user_type_id

    inner join sys.objects tblSO
            
        on tblSC.[object_id] = tblSO.[object_id]
    
    inner join sys.schemas tblSS
            
        on tblSO.[schema_id] = tblSS.[schema_id]

    inner join sys.default_constraints tblSDC

        on  tblSC.object_id = tblSDC.parent_object_id

        and tblSC.column_id = tblSDC.parent_column_id

    left outer join sys.sql_expression_dependencies AS tblSSED 
        
        on tblSC.[default_object_id] = tblSSED.referencing_id

    left outer join sys.objects tblSORef

        on tblSSED.referenced_id = tblSORef.[object_id]

    left outer join sys.schemas tblSORefS
        on tblSORef.[schema_id] = tblSORefS.[schema_id]

    order by

              tblSS.[name]

            , tblSO.[name]

            , tblSC.[name]
    
 end
 go

 /*
    We are on RDS, please skip marking as system object!'
 */
 if SUSER_ID('rdsa') is null
 begin

    print 'Marking [dbo].[sp_helpConstraintDefault] as system object ...'

    exec sp_MS_marksystemobject '[dbo].[sp_helpConstraintDefault]'
 
    print 'Marked [dbo].[sp_helpConstraintDefault] as system object!'

 end
 go

 set noexec off
 go

Output

Output – Sequence – #1

Output – Database – Idf – #1

 

Source Control

GitHub

Repository

DanielAdeniji/dbo.sp_helpConstraintDefault
Link

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