Sql Server - Get database constraints of all tables ( default and check constraints)

Question

I need to get constraints of all columns, tables in my database result like this :

DatabaseName | SchemaName | TableName | ColumnName | ColumnType | ConstraintName |

-

ConstraintType | Definition | CreateDate

I already used this answer but it shows just TableName, ColumnName, ConstraintName and definition.

Answer that I used:

SELECT 
    TableName = t.Name,
    ColumnName = c.Name,
    dc.Name,
    dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
ORDER BY t.Name

Thank you for your help.

So what's stopping you from adding those extra columns to your query? `sys.columns` has a column `system_type_id` and `user_type_id`, for example, and there's unsurprising an objects called `sys.types` and `sys.systypes`. — Thom A, Jul 24 '19 at 09:51
@Larnu I want both default and check constraints with those columns I have problem to reach those. — Mohammad Reza Shahrestani, Jul 24 '19 at 10:04

Khairul Alam · Accepted Answer · 2019-07-24T10:36:31.920

This may Help..

USE AdventureWorks2014
GO
;WITH ContraintDetails 
AS(

--DEFAULT_CONSTRAINT
SELECT  
    i.TABLE_CATALOG DatabaseName ,
    schema_name(t.schema_id) SchemaName,
    t.[name] TableName,   
    c.name as ColumnName,
    i.DATA_TYPE ColumnType,
    con.type_desc ConstantType,
    con.[name] ConstantName,
    col.[name] + ' : ' + con.[definition] Details
FROM sys.default_constraints con
INNER JOIN sys.objects t on con.parent_object_id = t.object_id
INNER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
INNER JOIN sys.columns c ON col.object_id = c.object_id AND col.column_id = c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS i ON c.name = i.COLUMN_NAME

UNION ALL

--CHECK_CONSTRAINT
SELECT 
    i.TABLE_CATALOG DatabaseName ,
    schema_name(t.schema_id) SchemaName,
    t.[name] TableName,  
    c.name as ColumnName,
    i.DATA_TYPE ColumnType,
    con.type_desc  ConstraintType,
    con.[name] as constraint_name,
    con.[definition] Details
FROM sys.check_constraints con
INNER JOIN sys.objects t on con.parent_object_id = t.object_id
INNER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
INNER JOIN sys.columns c ON col.object_id = c.object_id AND col.column_id = c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS i ON c.name = i.COLUMN_NAME

)
SELECT * FROM ContraintDetails
WHERE TableName = 'Employee' --AND  ColumnName = 'BirthDate'
ORDER BY TableName,ColumnName

Sql Server - Get database constraints of all tables ( default and check constraints)

1 Answers1