Enumerate all the indexes in a SQL Server database

Enumerate all the indexes in a SQL Server database

The indexing schema plays a fundamental role in performance tuning, and tools such as the Index Tuning Wizard that can suggest efficient schemas on the grounds of a database usage. Once the indexing schema has been completed, you should document it in order to know which tables have primary keys, which have clustered keys, which and how many are the non clustered indexes of a certain table. You can get this metadata through SQL Server Information Schema’s views, that are based on the following system tables: sysobjects, syscolumns, sysreferences, spt_values, and sysindexes.

Unfortunately these tables don’t provide all the necessary values, but the Books Online documentation lets to derive the meaning of all the fields in these system tables. The following sp_help_db_indexes stored procedure, placed inside the master database, lets to view all the indexes of each table for the current database, with the respective name, type and fields that make it up:

/* SP sp_help_db_indexes: enumerates all the indexes of all the DB’s tables Author : Giuseppe Dimauro */CREATE procedure sp_help_db_indexesASdeclare @empty varchar(1)select @empty = ”– 35 is the lenght of the name field of the master.dbo.spt_values tabledeclare @IgnoreDuplicateKeys varchar(35),    @Unique varchar(35),    @IgnoreDuplicateRows varchar(35),    @Clustered varchar(35),    @Hypotethical varchar(35),    @Statistics varchar(35),    @PrimaryKey varchar(35),    @UniqueKey varchar(35),    @AutoCreate varchar(35),    @StatsNoRecompute varchar(35)select @IgnoreDuplicateKeys = name from master.dbo.spt_values     where type = ‘I’ and number = 1 –ignore duplicate keysselect @Unique = name from master.dbo.spt_values     where type = ‘I’ and number = 2 –uniqueselect @IgnoreDuplicateRows = name from master.dbo.spt_values     where type = ‘I’ and number = 4 –ignore duplicate rowsselect @Clustered = name from master.dbo.spt_values     where type = ‘I’ and number = 16 –clusteredselect @Hypotethical = name from master.dbo.spt_values     where type = ‘I’ and number = 32 –hypotethicalselect @Statistics = name from master.dbo.spt_values     where type = ‘I’ and number = 64 –statisticsselect @PrimaryKey = name from master.dbo.spt_values     where type = ‘I’ and number = 2048 –primary keyselect @UniqueKey = name from master.dbo.spt_values     where type = ‘I’ and number = 4096 –unique keyselect @AutoCreate = name from master.dbo.spt_values     where type = ‘I’ and number = 8388608 –auto createselect @StatsNoRecompute = name from master.dbo.spt_values     where type = ‘I’ and number = 16777216 –stats no recomputeselect o.name,  i.name,  ‘index description’ = convert(varchar(210), –bits 16 off, 1, 2, 16777216 on      case when (i.status & 16)<>0 then @Clustered else ‘non’[email protected] end      + case when (i.status & 1)<>0 then ‘, ‘[email protected] else @empty end      + case when (i.status & 2)<>0 then ‘, ‘[email protected] else @empty end      + case when (i.status & 4)<>0 then ‘, ‘[email protected] else @empty end      + case when (i.status & 64)<>0 then ‘, ‘[email protected] else      case when (i.status & 32)<>0 then ‘, ‘[email protected] else @empty end end      + case when (i.status & 2048)<>0 then ‘, ‘[email protected] else @empty end      + case when (i.status & 4096)<>0 then ‘, ‘[email protected] else @empty end      + case when (i.status & 8388608)<>0 then ‘, ‘[email protected] else @empty end      + case when (i.status & 16777216)<>0 then ‘, ‘[email protected] else @empty end),  ‘index column 1’ = index_col(o.name,indid, 1),  ‘index column 2’ = index_col(o.name,indid, 2),  ‘index column 3’ = index_col(o.name,indid, 3)from sysindexes i, sysobjects owhere i.id = o.id and      indid > 0 and indid < 255 -all the clustered (=1), non clusterd (>1 and <251), and text or image (=255)       and o.type = 'U' -user table      --ignore the indexes for the autostat      and (i.status & 64) = 0 -index with duplicates      and (i.status & 8388608) = 0 -auto created index      and (i.status & 16777216)= 0 --stats no recompute      order by o.name

########################################################

This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as