SQL-Server

Quick Guide: SQL Server Table Indexes

Nirav Desai
By Nirav Desai
Jan 26, 2024
Quick Guide: SQL Server Table Indexes

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:

TAGS DBA SQL Table-Index
Quick Guide: SQL Server Table Constraints
SQL-Server
Quick Guide: SQL Server Table Constraints
Nirav Desai
Jan 28, 2024

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.!

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