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’+@Clustered end      + case when (i.status & 1)<>0 then ‘, ‘+@IgnoreDuplicateKeys else @empty end      + case when (i.status & 2)<>0 then ‘, ‘+@Unique else @empty end      + case when (i.status & 4)<>0 then ‘, ‘+@IgnoreDuplicateRows else @empty end      + case when (i.status & 64)<>0 then ‘, ‘+@Statistics else      case when (i.status & 32)<>0 then ‘, ‘+@Hypotethical else @empty end end      + case when (i.status & 2048)<>0 then ‘, ‘+@PrimaryKey else @empty end      + case when (i.status & 4096)<>0 then ‘, ‘+@UniqueKey else @empty end      + case when (i.status & 8388608)<>0 then ‘, ‘+@AutoCreate else @empty end      + case when (i.status & 16777216)<>0 then ‘, ‘+@StatsNoRecompute 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

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

devx-admin

devx-admin

Share the Post:
Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security.

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private,

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists

AI Tool

Unleashing AI Power with Microsoft 365 Copilot

Microsoft has recently unveiled the initial list of Australian clients who will benefit from Microsoft 365 (M365) Copilot through the exclusive invitation-only global Early Access Program. Prominent organizations participating in

Microsoft Egnyte Collaboration

Microsoft and Egnyte Collaboration

Microsoft has revealed a collaboration with Egnyte, a prominent platform for content cooperation and governance, with the goal of improving real-time collaboration features within Microsoft 365 and Microsoft Teams. This

Best Laptops

Top Programming Laptops of 2023

In 2023, many developers prioritize finding the best laptop for programming, whether at home, in the workplace, or on the go. A high-performing, portable, and user-friendly laptop could significantly influence

Renaissance Gaming Magic

AI Unleashes A Gaming Renaissance

In recent times, artificial intelligence has achieved remarkable progress, with resources like ChatGPT becoming more sophisticated and readily available. Pietro Schirano, the design lead at Brex, has explored the capabilities

New Apple Watch

The New Apple Watch Ultra 2 is Awesome

Apple is making waves in the smartwatch market with the introduction of the highly anticipated Apple Watch Ultra 2. This revolutionary device promises exceptional performance, robust design, and a myriad

Truth Unveiling

Unveiling Truths in Bowen’s SMR Controversy

Tony Wood from the Grattan Institute has voiced his concerns over Climate and Energy Minister Chris Bowen’s critique of the Coalition’s support for small modular nuclear reactors (SMRs). Wood points

Avoiding Crisis

Racing to Defy Looming Financial Crisis

Chinese property developer Country Garden is facing a liquidity challenge as it approaches a deadline to pay $15 million in interest associated with an offshore bond. With a 30-day grace

Open-Source Development

Open-Source Software Development is King

The increasingly digital world has led to the emergence of open-source software as a critical factor in modern software development, with more than 70% of the infrastructure, products, and services

Home Savings

Sensational Savings on Smart Home Security

For a limited time only, Amazon is offering massive discounts on a variety of intelligent home devices, including products from its Ring security range. Running until October 2 or while

Apple Unleashed

A Deep Dive into the iPhone 15 Pro Max

Apple recently unveiled its groundbreaking iPhone 15 Pro and iPhone 15 Pro Max models, featuring a revolutionary design, extraordinary display technology, and unrivaled performance. These new models are the first

Renewable Crypto Miners

Crypto Miners Embrace Renewable Energy?

As the cryptocurrency sector deals with the fallout from the FTX and Celsius exchange collapses, Bitcoin miners are increasingly exploring alternative energy sources to reduce expenses and maintain profitability. Specialists

Laptop Savings

The HP Omen 16 is a Gamer’s Dream

Best Buy is currently offering an unbeatable deal on the HP Omen 16 gaming laptop, giving potential buyers the chance to save a significant $720 on their purchase. Originally priced

How to Check for Vulnerabilities in Exchange Server

It is imperative to keep your systems and infrastructure up-to-date to mitigate security issues and loopholes, and to protect them against any known vulnerabilities and security risks. There are many