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


 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

 if SUSER_ID('rdsa') is null
 begin

    exec sp_MS_marksystemobject '[dbo].[sp_helpConstraintDefault]'

 end
 go

Output

Output – Sequence – #1

Output – Database – Idf – #1

 

Source Control

GitHub

Repository

DanielAdeniji/dbo.sp_helpConstraintDefault
Link

SQL Server – Identify Objects Missing Primary Key Constraints

Background

Forgot the NoSQL crowd for a minute; most OLTP databases are best defined based on a firm understanding of the system being designed.

What are the entities ( table ), which set of attributes uniquely identify a record ( primary key ), and how are they connected ( foreign keys ).

Primary Key

Outline

  1. sys.objects
    • User Tables
      • Type = ‘U’
    • Microsoft Tables
      • is_ms_shipped=1
  2.  sys.key_constraints
    • Column
      • parent_object_id
        • Refers to table
      • type
        • PK => Primary Key
        • UQ => Unique Constraint
  3. sys.indexes
    • Column
      • object_id
        • Refers to table
      • is_unique
        • Unique Index

Code


select 

              [schema]
                = tblSS.[name]

            , [table]
                = tblSO.[name]

            , [uniqueConstraint]
                = tblSKCUQ.[name]

            , [index]
                = tblSI.[name]

            , [indexIsUnique]
                = tblSI.[is_unique]

            , [indexIsUniqueConstraint]
                = tblSI.[is_unique_constraint]

from  sys.schemas tblSS

inner join sys.objects tblSO

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

left outer join sys.key_constraints tblSKPCPK

        on tblSO.[object_id] = tblSKPCPK.[parent_object_id]

        and tblSKPCPK.[type] = 'PK'

left outer join sys.key_constraints tblSKCUQ

        on tblSO.[object_id] = tblSKCUQ.[parent_object_id]

        and tblSKCUQ.[type] = 'UQ'

left outer join sys.indexes tblSI

        on tblSO.[object_id] = tblSI.[object_id]

        and tblSI.[is_unique] = 1

where tblSO.[type] = 'U'

and   tblSO.[is_ms_shipped] = 0

and   tblSKPCPK.[object_id] is null

order by
          tblSS.[name]
        , tblSO.[name]
        , tblSI.[name]
        , tblSKCUQ.[name]<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>

References

  1. Microsoft
    • SQL Docs
      • System Catalog Views
        • sys.key_constraints

Transact SQL :- Error – Msg 15138 – “The database principal owns a schema in the database, and cannot be dropped”

Background

Cleaning up a database as we move it from Development to Production.

Recreate

Drop User

SQL


if user_id('LAB\daniel') is not null
begin

	exec sp_droprolemember
			  @rolename = 'db_owner'
			, @membername = 'LAB\daniel'

	drop user [LAB\daniel];

end
go

Error

Msg 15138

Error Text


Msg 15138, Level 16, State 1, Line 115

The database principal owns a schema in the database, and cannot be dropped.

Error Image

TheDatabasePrincipalOwnsASchemaIntheDatabase.20190204.1119AM.PNG

 

Troubleshoot

Metadata

sys.schemas

SQL


select
          [schema]
            =tblSS.[name]

        , tblSS.schema_id

        , tblSS.principal_id

        , [principal]
            = user_name(tblSS.principal_id)

		, [principalIsFixedRole]
			= tblSDP.is_fixed_role

		, [principalType]
			= tblSDP.[type_desc]

		--, tblSDP.*

from   sys.schemas tblSS

inner join sys.database_principals tblSDP

		on tblSS.principal_id = tblSDP.principal_id 

order by
    user_name(tblSS.principal_id) asc

Output

sys.schemas.20190204.1134AM.PNG

Explanation

  1. Schema
    • db_datareader
      • The owner for the db_datareader schema has been assigned to a database account, other than itself

Remediate

Change Schema Owner

ALTER AUTHORIZATION

SQL


ALTER AUTHORIZATION ON SCHEMA::db_datareader
   TO [db_datareader]
   ;