mkEagles
Reputation: 338
SQL Server get constraint column informations
I would like to write a DB query for SQL Server to get this column information:
- Column name (I have it)
- Column description (I have it)
- Data type (I have it)
- Constraints (is primary key, foreign key, is null, check constraint, default constraint, unique)
- Is index (I have it)
My current query is this (I would like to include check constraint and default constraint in a separate column):
SELECT
c.name AS 'name', t.name AS 'dataType',
t.name + '(' + CAST(c.max_length AS varchar(50)) +')' AS 'fullType',
ISNULL(sep.value,'') [Description],
CASE
WHEN c.is_nullable = 1 THEN 'null' ELSE 'not null'
END AS 'Constraint',
CASE
WHEN fk.object_id IS NOT NULL THEN 'foreign key' ELSE NULL
END AS relation,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name AS referenceTable,
ISNULL(i.is_primary_key, 0) AS 'isprimarykey',
i.name as 'index'
FROM
sys.tables tab
INNER JOIN
sys.columns c ON c.object_id = tab.object_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT OUTER JOIN
sys.foreign_key_columns fk_cols ON fk_cols.parent_object_id = tab.object_id
AND fk_cols.parent_column_id = c.column_id
LEFT OUTER JOIN
sys.foreign_keys fk ON fk.object_id = fk_cols.constraint_object_id
LEFT OUTER JOIN
sys.tables pk_tab ON pk_tab.object_id = fk_cols.referenced_object_id
LEFT OUTER JOIN
sys.columns pk_col ON pk_col.column_id = fk_cols.referenced_column_id
AND pk_col.object_id = fk_cols.referenced_object_id
LEFT JOIN
sys.extended_properties sep ON c.object_id = sep.major_id
AND c.column_id = sep.minor_id
AND sep.name = 'MS_Description'
JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = Object_id('sometableName')
Upvotes: 0
Views: 84
Answers (1)
Esat Erkec
Reputation: 1724
To overcome this issue, we need to use sys.check_constraints and sys.default_constraints. At first, we need to create an inline table-valued function because one column can contain more than one constraint. The following query will create a table-valued parameter and it returns the default and check constraint details.
CREATE FUNCTION ContraintView
( @ColId AS INT , @ColIndexId AS INT)
RETURNS TABLE
AS
RETURN
SELECT * FROM (
select schema_name(t.schema_id) + '.' + t.[name] AS ConstraintName,
'Check constraint' AS constraint_type,
con.[name] as constraint_name,
con.[definition] ,
col.object_id,
col.column_id
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition] ,
col.object_id ,
col.column_id
from sys.default_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
) AS TMP_TBL
WHERE TMP_TBL.object_id =@ColId AND TMP_TBL.column_id = @ColIndexId
And then, we will apply this function to your query;
SELECT * FROM (
SELECT
c.name AS 'name', t.name AS 'dataType',
t.name + '(' + CAST(c.max_length AS varchar(50)) +')' AS 'fullType',
ISNULL(sep.value,'') [Description],
CASE
WHEN c.is_nullable = 1 THEN 'null' ELSE 'not null'
END AS 'Constraint',
CASE
WHEN fk.object_id IS NOT NULL THEN 'foreign key' ELSE NULL
END AS relation,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name AS referenceTable,
ISNULL(i.is_primary_key, 0) AS 'isprimarykey',
i.name as 'index',
c.object_id ,
c.column_id
FROM
sys.tables tab
INNER JOIN
sys.columns c ON c.object_id = tab.object_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT OUTER JOIN
sys.foreign_key_columns fk_cols ON fk_cols.parent_object_id = tab.object_id
AND fk_cols.parent_column_id = c.column_id
LEFT OUTER JOIN
sys.foreign_keys fk ON fk.object_id = fk_cols.constraint_object_id
LEFT OUTER JOIN
sys.tables pk_tab ON pk_tab.object_id = fk_cols.referenced_object_id
LEFT OUTER JOIN
sys.columns pk_col ON pk_col.column_id = fk_cols.referenced_column_id
AND pk_col.object_id = fk_cols.referenced_object_id
LEFT JOIN
sys.extended_properties sep ON c.object_id = sep.major_id
AND c.column_id = sep.minor_id
AND sep.name = 'MS_Description'
JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = Object_id('HumanResources.Employee')
) AS TMP_TBL OUTER APPLY dbo.ContraintView( TMP_TBL.object_id ,TMP_TBL.column_id)
The output will be like
+-------------------+------------------+----------------------+-------------------------------------------------------------------------------------------------------------------------+------------+-------------+----------------+--------------+------------------------------------------------+-----------+-----------+-------------------------+--------------------+----------------------------+----------------------------------------------------------------------------+-----------+-----------+
| name | dataType | fullType | Description | Constraint | relation | referenceTable | isprimarykey | index | object_id | column_id | ConstraintName | constraint_type | constraint_name | definition | object_id | column_id |
+-------------------+------------------+----------------------+-------------------------------------------------------------------------------------------------------------------------+------------+-------------+----------------+--------------+------------------------------------------------+-----------+-----------+-------------------------+--------------------+----------------------------+----------------------------------------------------------------------------+-----------+-----------+
| BusinessEntityID | int | int(4) | Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID. | not null | foreign key | Person.Person | 1 | PK_Employee_BusinessEntityID | 981578535 | 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| NationalIDNumber | nvarchar | nvarchar(30) | Unique national identification number such as a social security number. | not null | NULL | NULL | 0 | AK_Employee_NationalIDNumber | 981578535 | 2 | NULL | NULL | NULL | NULL | NULL | NULL |
| LoginID | nvarchar | nvarchar(512) | Network login. | not null | NULL | NULL | 0 | AK_Employee_LoginID | 981578535 | 3 | NULL | NULL | NULL | NULL | NULL | NULL |
| OrganizationNode | hierarchyid | hierarchyid(892) | Where the employee is located in corporate hierarchy. | null | NULL | NULL | 0 | IX_Employee_OrganizationNode | 981578535 | 4 | NULL | NULL | NULL | NULL | NULL | NULL |
| OrganizationNode | hierarchyid | hierarchyid(892) | Where the employee is located in corporate hierarchy. | null | NULL | NULL | 0 | IX_Employee_OrganizationLevel_OrganizationNode | 981578535 | 4 | NULL | NULL | NULL | NULL | NULL | NULL |
| OrganizationLevel | smallint | smallint(2) | The depth of the employee in the corporate hierarchy. | null | NULL | NULL | 0 | IX_Employee_OrganizationLevel_OrganizationNode | 981578535 | 5 | NULL | NULL | NULL | NULL | NULL | NULL |
| JobTitle | nvarchar | nvarchar(100) | Work title such as Buyer or Sales Representative. | not null | NULL | NULL | 0 | NULL | 981578535 | 6 | NULL | NULL | NULL | NULL | NULL | NULL |
| BirthDate | date | date(3) | Date of birth. | not null | NULL | NULL | 0 | NULL | 981578535 | 7 | HumanResources.Employee | Check constraint | CK_Employee_BirthDate | ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())) | 981578535 | 7 |
| MaritalStatus | nchar | nchar(2) | M = Married, S = Single | not null | NULL | NULL | 0 | NULL | 981578535 | 8 | HumanResources.Employee | Check constraint | CK_Employee_MaritalStatus | (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M') | 981578535 | 8 |
| Gender | nchar | nchar(2) | M = Male, F = Female | not null | NULL | NULL | 0 | NULL | 981578535 | 9 | HumanResources.Employee | Check constraint | CK_Employee_Gender | (upper([Gender])='F' OR upper([Gender])='M') | 981578535 | 9 |
| HireDate | date | date(3) | Employee hired on this date. | not null | NULL | NULL | 0 | NULL | 981578535 | 10 | HumanResources.Employee | Check constraint | CK_Employee_HireDate | ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())) | 981578535 | 10 |
| SalariedFlag | Flag | Flag(1) | Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining. | not null | NULL | NULL | 0 | NULL | 981578535 | 11 | HumanResources.Employee | Default constraint | DF_Employee_SalariedFlag | SalariedFlag = ((1)) | 981578535 | 11 |
| VacationHours | smallint | smallint(2) | Number of available vacation hours. | not null | NULL | NULL | 0 | NULL | 981578535 | 12 | HumanResources.Employee | Check constraint | CK_Employee_VacationHours | ([VacationHours]>=(-40) AND [VacationHours]<=(240)) | 981578535 | 12 |
| VacationHours | smallint | smallint(2) | Number of available vacation hours. | not null | NULL | NULL | 0 | NULL | 981578535 | 12 | HumanResources.Employee | Default constraint | DF_Employee_VacationHours | VacationHours = ((0)) | 981578535 | 12 |
| SickLeaveHours | smallint | smallint(2) | Number of available sick leave hours. | not null | NULL | NULL | 0 | NULL | 981578535 | 13 | HumanResources.Employee | Check constraint | CK_Employee_SickLeaveHours | ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)) | 981578535 | 13 |
| SickLeaveHours | smallint | smallint(2) | Number of available sick leave hours. | not null | NULL | NULL | 0 | NULL | 981578535 | 13 | HumanResources.Employee | Default constraint | DF_Employee_SickLeaveHours | SickLeaveHours = ((0)) | 981578535 | 13 |
| CurrentFlag | Flag | Flag(1) | 0 = Inactive, 1 = Active | not null | NULL | NULL | 0 | NULL | 981578535 | 14 | HumanResources.Employee | Default constraint | DF_Employee_CurrentFlag | CurrentFlag = ((1)) | 981578535 | 14 |
| rowguid | uniqueidentifier | uniqueidentifier(16) | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. | not null | NULL | NULL | 0 | AK_Employee_rowguid | 981578535 | 15 | HumanResources.Employee | Default constraint | DF_Employee_rowguid | rowguid = (newid()) | 981578535 | 15 |
| ModifiedDate | datetime | datetime(8) | Date and time the record was last updated. | not null | NULL | NULL | 0 | NULL | 981578535 | 16 | HumanResources.Employee | Default constraint | DF_Employee_ModifiedDate | ModifiedDate = (getdate()) | 981578535 | 16 |
| BusinessEntityID | int | int(4) | Clustered index created by a primary key constraint. | not null | foreign key | Person.Person | 1 | PK_Employee_BusinessEntityID | 981578535 | 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| NationalIDNumber | nvarchar | nvarchar(30) | Unique nonclustered index. | not null | NULL | NULL | 0 | AK_Employee_NationalIDNumber | 981578535 | 2 | NULL | NULL | NULL | NULL | NULL | NULL |
| LoginID | nvarchar | nvarchar(512) | Unique nonclustered index. | not null | NULL | NULL | 0 | AK_Employee_LoginID | 981578535 | 3 | NULL | NULL | NULL | NULL | NULL | NULL |
| OrganizationLevel | smallint | smallint(2) | Unique nonclustered index. | null | NULL | NULL | 0 | IX_Employee_OrganizationLevel_OrganizationNode | 981578535 | 5 | NULL | NULL | NULL | NULL | NULL | NULL |
| JobTitle | nvarchar | nvarchar(100) | Unique nonclustered index. | not null | NULL | NULL | 0 | NULL | 981578535 | 6 | NULL | NULL | NULL | NULL | NULL | NULL |
| BirthDate | date | date(3) | Unique nonclustered index. Used to support replication samples. | not null | NULL | NULL | 0 | NULL | 981578535 | 7 | HumanResources.Employee | Check constraint | CK_Employee_BirthDate | ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())) | 981578535 | 7 |
+-------------------+------------------+----------------------+-------------------------------------------------------------------------------------------------------------------------+------------+-------------+----------------+--------------+------------------------------------------------+-----------+-----------+-------------------------+--------------------+----------------------------+----------------------------------------------------------------------------+-----------+-----------+
Upvotes: 1
Related Questions
- How do I get constraints on a SQL Server table column
- How to get all constraint on table?
- SQL Server 2008- Get table constraints
- get text for constraints in sql server
- How to get column-wise constraints in SQL Server
- How to get table constraint information in SQL Server
- How to get the constraints on a table
- How to get list of constraints of a table along with their respective column names in SQL Server 2008 R2
- How can we find the name of the constraint from the Database.?
- How to find which column corresponds to a constraint?