SQL-Server

Quick Guide: SQL Server Table Constraints

Nirav Desai
By Nirav Desai
Jan 28, 2024
Quick Guide: SQL Server Table Constraints

If you want to have a list of constraints applied on a particular table in the SQL server, this will help you to get it in one go.

 

DECLARE @TABLENAME VARCHAR(50) = '<table_name>'

SELECT ObjectName
    ,TypeOfObject
    ,TypeOfConstraint
    ,ConstraintName
    ,ConstraintDescription
FROM (
    SELECT schema_name(t.schema_id) + '.' + t.[name] AS ObjectName
        ,CASE 
            WHEN t.[type] = 'U'
                THEN 'Table'
            WHEN t.[type] = 'V'
                THEN 'View'
            END AS [TypeOfObject]
        ,CASE 
            WHEN c.[type] = 'PK'
                THEN 'Primary key'
            WHEN c.[type] = 'UQ'
                THEN 'Unique constraint'
            WHEN i.[type] = 1
                THEN 'Unique clustered index'
            WHEN i.type = 2
                THEN 'Unique index'
            END AS TypeOfConstraint
        ,ISNULL(c.[name], i.[name]) AS ConstraintName
        ,SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [ConstraintDescription]
    FROM sys.objects t
    LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id
    LEFT OUTER JOIN sys.key_constraints c ON i.object_id = c.parent_object_id
        AND i.index_id = c.unique_index_id
    CROSS APPLY (
        SELECT col.[name] + ', '
        FROM sys.index_columns ic
        INNER JOIN sys.columns col ON ic.object_id = col.object_id
            AND ic.column_id = col.column_id
        WHERE ic.object_id = t.object_id
            AND ic.index_id = i.index_id
        ORDER BY col.column_id
        FOR XML path('')
        ) D(column_names)
    WHERE is_unique = 1
        AND t.name = @TABLENAME
        AND t.is_ms_shipped <> 1
    
    UNION ALL
    
    SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name AS foreign_table
        ,'Table'
        ,'Foreign key'
        ,fk.name AS fk_ConstraintName
        ,cols.[name] + ' REFERENCES ' + schema_name(pk_tab.schema_id) + '.' + pk_tab.name + ' (' + c2.[name] + ')'
    FROM sys.foreign_keys fk
    INNER JOIN sys.tables fk_tab ON fk_tab.object_id = fk.parent_object_id
    INNER JOIN sys.tables pk_tab ON pk_tab.object_id = fk.referenced_object_id
    INNER JOIN sys.foreign_key_columns fk_cols ON fk_cols.constraint_object_id = fk.object_id
    INNER JOIN sys.columns cols ON cols.object_id = fk_cols.parent_object_id AND cols.column_id = fk_cols.parent_column_id
    INNER JOIN sys.columns c2 ON c2.object_id = fk_cols.referenced_object_id AND c2.column_id = fk_cols.referenced_column_id
    WHERE fk_tab.name = @TABLENAME
        OR pk_tab.name = @TABLENAME
    
    UNION ALL
    
    SELECT schema_name(t.schema_id) + '.' + t.[name]
        ,'Table'
        ,'Check constraint'
        ,con.[name] AS ConstraintName
        ,con.[definition]
    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
    WHERE t.name = @TABLENAME
    
    UNION ALL
    
    SELECT schema_name(t.schema_id) + '.' + t.[name]
        ,'Table'
        ,'Default constraint'
        ,con.[name]
        ,col.[name] + ' = ' + con.[definition]
    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
    WHERE t.name = @TABLENAME
    ) a
ORDER BY ObjectName
    ,TypeOfConstraint
    ,ConstraintName

 

Output:

Enjoy.!

TAGS DBA SQL
Quick Guide: SQL Server Table Indexes
SQL-Server
Quick Guide: SQL Server Table Indexes
Nirav Desai
Jan 26, 2024

As in recent work with the client, I got the question of finding the indexes to be applied on a particular table in the SQL server. If you want to have listed all the indexes from a particular table from the SQL server, then now you just have to write your table name in the variable and execute the below query. And see the result.   DECLARE @TABLENAME VARCHAR(50) = '<table_name>' SELECT '[' + s.name + '].[' + sObj.name + ']' AS 'TableName'     ,+ ind.name AS 'IndexName'     ,ind.type_desc AS 'IndexType'     ,STUFF((             SELECT ', [' + sc.name + ']' AS "text()"             FROM syscolumns AS sc             INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id                 AND ic.column_id = sc.colid             WHERE sc.id = Obj.object_id                 AND ic.index_id = sind.indid                 AND ic.is_included_column = 0             ORDER BY key_ordinal             FOR XML PATH('')             ), 1, 2, '') AS 'IndexedColumns' FROM sysindexes AS sind INNER JOIN sys.indexes AS ind ON ind.object_id = sind.id     AND ind.index_id = sind.indid INNER JOIN sysobjects AS sObj ON sObj.id = sind.id INNER JOIN sys.objects AS Obj ON Obj.object_id = sObj.id     AND is_ms_shipped = 0 INNER JOIN sys.schemas AS s ON s.schema_id = Obj.schema_id WHERE ind.object_id = OBJECT_ID(@TABLENAME)     AND ind.is_primary_key = 0     AND ind.is_unique = 0     AND ind.is_unique_constraint = 0 ORDER BY TableName     ,IndexName;   Output:

Read More
Quick Guide: Table Partitioning in SQL
SQL-Server
Quick Guide: Table Partitioning in SQL
Nirav Desai
Jun 10, 2020

What is table partitioning in SQL? Table partitioning is a way to divide a large table into smaller, more manageable parts without having to create separate tables for each part. Data in a partitioned table is physically stored in groups of rows called partitions and each partition can be accessed and maintained separately. Partitioning is not visible to end-users, a partitioned table behaves like one logical table when queried. Data in a partitioned table is partitioned based on a single column, the partition column often called the partition key. Only one column can be used as the partition column, but it is possible to use a computed column. The partition scheme maps the logical partitions to physical filegroups. It is possible to map each partition to its own filegroup or all partitions to one filegroup.

Read More
Quick Guide Restoring Encrypted Database
SQL-Server
Quick Guide Restoring Encrypted Database
Nirav Desai
Jun 01, 2020

We have to add the below script in the master database to restore an encrypted database. CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your_password>' CREATE CERTIFICATE <your_certificate_name> FROM File = '<path of.cer file>' WITH PRIVATE KEY (FILE = 'path of .pvk file', DECRYPTION BY PASSWORD = '<your_password>');   Now you have to do follow the normal restore process in SQL.

Read More
Nirav Desai

About the Author

Nirav Desai

I am a seasoned software professional with over 9 years of experience in .NET technologies and SQL. Currently, I am working as a Team Leader at MagnusMinds IT Solution, managing multiple projects in collaboration with my team.

My expertise includes .NET C#, ASP.NET MVC, .NET Core, RESTful API development, and SQL database management. With a strong technical foundation and leadership skills, I focus on delivering high-quality solutions while fostering teamwork and innovation within my projects.

RECENT POSTS

CATEGORIES

TAGS CLOUD