Query machine name of the sql server instance - the hard or the harder way

I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was partly because of legacy reasons and partly because it supports clustering across all major hardware, operating systems, and applications, including SQL Server. SQL Server instances are set up in either 2 or 3 nodes, active/passive, active/active, etc., configurations. There are approximately 30 physical servers hosting SQL Server instances. Yes, the client is going to move all the SQL workloads to Always On Clusters, but the process has been slow because all the databases are used for COTS/third-party applications.

A virtual name is used to make a connection to a SQL Server instance. Sometimes, I would need to know the actual physical node name where a particular SQL instance is active, and I needed to find it out programmatically.

You may have different reasons for connecting to SQL Server using a virtual name but need to know the underlying machine name.

So I first tried this query:

SELECT   

  @@SERVERNAME ServerName_Global_Variable
 ,SERVERPROPERTY('ServerName') ServerName
 ,SERVERPROPERTY('InstanceName') InstanceName
 ,SERVERPROPERTY('MachineName') MachineName
 ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') NetBIOS

On a failover cluster, MachineName returns the clustered/virtual SQL Server 
name, while ComputerNamePhysicalNetBIOS returns the actual active node’s 
physical name. That means MachineName is better when you want the SQL 
instance identity, and ComputerNamePhysicalNetBIOS is better when you want 
the underlying host currently 
running the instance.

It would be the easier way if it worked in this situation. Alas, it still kept giving me virtual server name.

Then I tried using a DOS command, assuming you have or are able to temporarily turn on XP_CMDSHELL.

EXEC master..XP_CMDSHELL 'Hostname'

And I still got the virtual server name.

Then I tried the following which does display the actual server name in one of the messages:

EXEC master..xp_cmdshell 'net user'

Mission accomplished, great! I could write additional code around it to trim out every other text from it, extracting only the computer name, and then store it in a variable or in my own metadata table for further processing, reporting, etc.

Then I thought, is there another way, perhaps a better way? Well, I wouldn't necessarily call my next approach better, but it's certainly another way.

If you're looking for a one-liner to remotely look up a computer name, simply run the following command from the command prompt or PowerShell:

wmic /NODE:sqlservernode1 computersystem get Name

Below is a bit lengthy TSQL code to do this while connected to a SQL instance. This also detects whether the local computer name in sys.servers (where server_id = 0) differs from the actual computer name. This typically occurs when the computer is renamed at the operating system level but the local server name has not been updated in SQL Server.

Note that if the XP_CMDSHELL is disabled,  it temporarily turns it on then off when done. 

/**********************************************************************
-- Script Purpose:
--   Retrieve the current computer/server name using WMIC via xp_cmdshell.
--
-- Description:
--   This script:
--     1. Checks if xp_cmdshell is enabled.
--     2. Enables it temporarily if needed.
--     3. Executes "wmic computersystem get Name" to get the hostname.
--     4. Captures and parses the output from xp_cmdshell.
--     5. Returns the server name as a single value.
--     6. Reverts xp_cmdshell to its prior state if it was originally disabled.
--
-- Requirements:
--   - Requires sysadmin privileges.
--   - xp_cmdshell must be available for use (can be toggled dynamically).
--
-- Notes:
--   - Uses a temporary table (#t1_xp_cmdshell_output) to capture WMIC output.
--   - Assumes SQL Server runs under an account with permission to execute WMIC.
--
-- Author: 
-- Last Updated:   2026-02-24
**********************************************************************/

SET NOCOUNT ON;

DECLARE @computer_name VARCHAR(500);
DECLARE @xp_cmdshell_status BIT;

-- Check the current xp_cmdshell configuration state
SELECT @xp_cmdshell_status = CAST(value_in_use AS BIT)
FROM sys.configurations
WHERE name = 'xp_cmdshell';

-- Enable xp_cmdshell temporarily if it's currently disabled
IF @xp_cmdshell_status = 0
BEGIN
    PRINT 'xp_cmdshell is disabled on this server. Temporarily enabling it...';

    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;
END;

-- Drop temp table if it exists
IF OBJECT_ID('tempdb..#t1_xp_cmdshell_output') IS NOT NULL
    DROP TABLE #t1_xp_cmdshell_output;

-- Create a table to capture xp_cmdshell output
CREATE TABLE #t1_xp_cmdshell_output
(
    id INT IDENTITY(1,1),
    txt NVARCHAR(2000)
);

-- Execute WMIC command to fetch the computer name
INSERT INTO #t1_xp_cmdshell_output
EXEC master..xp_cmdshell 'wmic computersystem get Name';

-- Extract the first non‑empty line that is not the header
SELECT TOP 1 @computer_name = LTRIM(RTRIM(REPLACE(REPLACE(txt, CHAR(13), ''), CHAR(10), '')))
FROM #t1_xp_cmdshell_output
WHERE txt IS NOT NULL
  AND LTRIM(RTRIM(txt)) <> ''
  AND LOWER(LTRIM(RTRIM(txt))) NOT LIKE 'name%';

-- Return the detected computer name
SELECT @computer_name AS computer_name;

-- warn if detected name and SERVERPROPERTY('MachineName') differ (case‑insensitive)
IF UPPER(@computer_name) != UPPER(CAST(SERVERPROPERTY('MachineName') AS VARCHAR(500)))
BEGIN
    SELECT 'SERVERPROPERTY(''MachineName'') DOES NOT MATCH THE HOST NAME' AS WarningMessage;
END;

-- Restore xp_cmdshell to its original configuration if it was disabled earlier
IF @xp_cmdshell_status = 0
BEGIN
    PRINT 'Returning xp_cmdshell to its disabled state...';

    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
END;

Generally, when I'm using a DOS or PowerShell command, I prefer using PowerShell to populate the data in the SQL table, avoiding all the additional code I've used in the above T-SQL block.

And finally, there is one more way, sort of! If you execute a multi-server query with the "Add server name to the results" option set to true (default), it will display the physical server name in the results pane. However, I'm not aware of a way to capture it dynamically in a variable at this point.

If you think I missed something, please don't hesitate to provide feedback!

Powershell script to find new servers in an AD domain

This post is part of a process I'm developing to automatically discover SQL Server instances within an Active Directory domain. Expect a series of several related posts.

You might wonder if I'm reinventing the wheel. In many cases, you'd be right. However, as a consultant and visiting DBA, I have valid reasons for this approach. Luckily, I already possess the necessary scripts, so this endeavor mainly revolves around automating the entire process.

This is part one of the series. It identifies new servers added to AD. At this point, we can't ascertain if any of these servers are SQL Servers. That topic will be addressed in the subsequent blog post at:

https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-sql-server.html

---

The script below, written in PowerShell, displays the results of the discovery on the console (for up to 100 servers). It also exports the findings to a CSV file named 'new_servers.csv'. Feel free to modify or comment out any part as you see fit.

Before executing this script, kindly review and modify the default values for the variables as necessary.

<#

You can use this to get list of all servers in an AD domain or
new servers added in last X days, or any other properties
you want to filter the results on.

You should not need to be a domain admin or 
need any special permission in the AD.
This might change in the future versions though.


You will need powershell active directory module installed 
on the computer where you are running this script from.

If you are using a Windows 10 machine like I am right now, 
here is a good resource to get the AD module installed.
https://gallery.technet.microsoft.com/Install-the-Active-fd32e541


#>
try
{

# filter by servers added in last n days
# or set this to 0 for all servers
$days_to_search = 30

# if searching in different domain than your current domain, 
# specifiy the domain name between the double quotes
$domain = ""                

if ($domain -eq "")
{
        $domain = Get-ADDomain 
}
else
{
        $domain = Get-ADDomain -Identity $domain
}


$domain_name = $domain.name
$distinguished_name = $domain.DistinguishedName
$domain_controller = (Get-ADDomainController -server $domain_name).HostName

$search_base = "OU=SERVERS," + $distinguished_name
$export_file_name = $env:USERPROFILE + "\Documents" + "\new_servers.csv"


# convert $days_to_search to a negative value
if($days_to_search -lt 0) {$days_to_search = -$days_to_search}


[String](Get-Date) + ": Begin searching for new servers in the AD domain"
"-------------------------------------------------------"

if($days_to_search -lt 0)
{
   $date_filter = (get-date).adddays($days_to_search)
   "Date filter value: " + $date_filter
   $search_filter = {Created -gt $date_filter -and operatingsystem -like "Windows Server*"}
   "Find new computers added in last " + $days_to_search + " days to AD domain (" + $domain_name + ")"

}
else
{
   $search_filter = {operatingsystem -like "Windows Server*"}
   "Find all servers in AD domain (" + $domain_name + ")"
}
"Search Base: $search_base"
"Domain controller: " + $domain_controller


$computers = @()
$computers += (get-adcomputer -SearchBase  $search_base -Properties * -Filter $search_filter -server $domain_controller)

[String](Get-Date) + ": Total Number of Servers Found: " + $Computers.Count

# Display the results on the console 
"Displaying first 100 results...."
$computers | Select-Object Name, 
                           Created, 
                           IPv4Address,
                           OperatingSystem,
                           OperatingSystemVersion  -First 100 | ft -AutoSize


# Exports results to a CSV file
[String](Get-Date) + ": Exporting results to ($export_file_name)...."
$computers | Select-Object Name, 
                           Created, 
                           DNSHostName,
                           IPv4Address,
                           OperatingSystem,
                           OperatingSystemHotfix,
                           OperatingSystemServicePack,
                           OperatingSystemVersion, 
                           IPv6Address,
                           DistinguishedName, 
                           createTimeStamp, 
                           Description | Export-CSV `
                                         $export_file_name -NoTypeInformation `
                                                           -Encoding UTF8


[String](Get-Date) + ": End searching for new computers"

}
Catch
{
    [String](Get-Date) + ": Error occurred"
    throw
   
}

Download this PowreShell script From GitHub

Caveats:

This script is specifically tailored for organizations where all servers are registered in Active Directory (AD). If there are new servers set up as non-AD, standalone units within private DMZs, this script will not detect them.

Additionally, the script operates under the assumption that all servers, SQL Servers included, are registered under the 'SERVERS' Organizational Unit (OU) in AD. Should your organization utilize a different OU, or if you wish to scan all OUs (which might include computers running non-server Windows editions), you'll need to adjust the $search_base variable.

At its current configuration, the script searches one domain at a time. By default, it's set to your current domain. However, it can be adjusted to target any domain within the AD forest, given you have access to it or if there's a trust relationship established between your authentication domain and the target domain. Further development can enhance this script to scan all domains in an AD forest.

Find SQL Server Indexes With Heavy Maintenance Overhead

Everyone knows indexes are critical for query performance but comes with overhead: For every single INSERT, UPDATE, and DELETE operation SQL SErver has to maintain every index on that table. 

For example, when you INSERT a row, SQL Server:

• Finds the right leaf page in every index 
• Splits pages if needed (fragmentation!)
• Updates statistics on every index
• Writes to transaction log for every index change

So ror a table with 10 indexes, that's 10x the I/O, 10x the log writes, 10x the CPU cycles, all for just one INSERT.

Usually, this overhead is worth the performance gain we get out of them. But some indexes create heavy maintenance overhead while sitting mostly unused for queries or constraints. I've seen this most often in massive data warehouses, but it can cripple OLTP performance too.

The following script finds exactly those culprits:

---

-- See blog post: https://sqlpal.blogspot.com/2019/05/do-you-have-rarely-used-indexes-that.html
SET NOCOUNT ON;
USE [Your Database Name];
/*==========================================================================================
  Script Name : Find-Heavy-Maintenance-Low-Usage-Indexes.sql

  Purpose:
      Identifies indexes that incur heavy maintenance overhead (lots of writes) 
      but provide little query benefit (few reads). These are prime candidates for removal.

  Logic:
      - Calculates total reads (seeks + scans + lookups) vs writes (updates) from dm_db_index_usage_stats
      - Filters for indexes with write_to_read_ratio > 10 (mostly write overhead)
      - Only non-unique indexes with significant activity (>1M writes, <1K reads)
      - Helps find indexes that hurt more than they help

  Thresholds (tune for your environment):
      - total_user_writes > 1,000,000  (significant maintenance cost)
      - total_user_reads < 1,000       (minimal query benefit)
      - write_to_read_ratio > 10       (writes >> reads)

  Prerequisites:
      - Run after server uptime of 24+ hours for reliable usage stats
      - dm_db_index_usage_stats resets on server restart/index rebuild

     SAFETY WARNING:
      - Stats reset on SQL restart, index rebuilds, or stats updates
      - Review execution plans before dropping ANY index
      - Test in dev first since some "write-heavy" indexes serve critical constraints

==========================================================================================*/

WITH index_usage AS
(
    SELECT 
        DB_NAME(iu.database_id) AS db_name,
        OBJECT_NAME(iu.object_id, iu.database_id) AS object_name,
        i.name AS index_name,
        i.type_desc AS index_type,
        
        -- Total read operations (query benefit)
        SUM(iu.user_seeks + iu.user_scans + iu.user_lookups) AS total_user_reads,
        
        -- Total write operations (maintenance cost)
        SUM(iu.user_updates) AS total_user_writes
        
    FROM sys.dm_db_index_usage_stats iu
    INNER JOIN sys.indexes i 
        ON i.object_id = iu.object_id 
        AND i.index_id = iu.index_id
    
    WHERE 
        iu.database_id = DB_ID() 
        AND i.index_id > 0
        AND i.is_unique = 0
        
    GROUP BY 
        iu.database_id,
        iu.object_id,
        i.name,
        i.type_desc
)

SELECT 
    *,
    
    -- Write-to-read ratio (higher = more maintenance overhead)
    total_user_writes * 1.0 / NULLIF(total_user_reads, 0) AS write_to_read_ratio

FROM index_usage

WHERE 
    -- High maintenance cost
    total_user_writes > 1000000                    -- 1M+ writes = significant overhead
    
    -- Low/no query benefit
    AND total_user_reads < 1000                    -- <1K reads = rarely used
    
    -- Mostly write overhead
    AND (
        total_user_writes * 1.0 / NULLIF(total_user_reads, 0) > 10  -- 10:1 write bias
        OR total_user_reads = 0                                    -- Never used
    )

ORDER BY write_to_read_ratio DESC;  -- Worst offenders first

/*
  USAGE TIPS:
  
  1. Run after 24+ hours uptime for reliable stats
  2. Higher thresholds = fewer but more certain candidates
  3. Check execution plans before dropping ANY index
  4. Consider business constraints (FKs, app assumptions)
  
  EXAMPLE THRESHOLD ADJUSTMENTS:
  -- More aggressive:
  -- total_user_writes > 500000 AND total_user_reads < 500
  
  -- More conservative:
  -- total_user_writes > 5000000 AND total_user_reads < 100
*/

---

Before dropping anything: Validate with execution plans and test workload impact first.

Using Extended Events To Capture Backup and Restore Progress

If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is every approximately 10%. You can change that frequency interval by specifying STATS [ = percentage ] option.


BACKUP DATABASE [AdminDBA]
TO  DISK = N'O:\MSSQL13.SQL2016AG01\MSSQL\Backup\AdminDBA.bak' WITH
NOFORMAT, NOINIT, 
NAME = N'AdminDBA-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD, 
STATS = 10
GO

But what if the backup/restore was started from a different session that you don't have access (another DBA, scheduled job etc.) or you need more information to troubleshoot issues?

Here I should first mention that there are already couple options to track the progress.

You could review or query the sql server error logs (unless trace flag 3226 is enabled). By default its disabled. You can if that trace flag is enabled using:

DBCC TRACESTATUS(3226);

If trace flag 3226 is enabled, the successful backup messages are suppressed in the error log.

Or you could use one of the popular DMVs:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')


Starting with SQL Server 2016, you can turn on the backup_restore_progress_trace extended event to trace both Backup and Restore progress. It comes with rich collection and diagnostic capabilities of extended events that will give you great insight into backup/restore operations and help you troubleshoot any issues better.

Here is how to setup the trace using SSMS:

Connect to the SQL Server then expand all way up to the Extended Events -> Sessions, right click and select New Session Wizard:

Accept the welcome page and on the next page give the session a name. If you choose to you can check box against the Start the event session at Server Startp. I will leave it unchecked though.



There is no built in trace template so leave the Do not use a template selected and click next.

On the next screen, type in "backup" to search in the EventLibrary. select the "backup_restore_progress_trace" then click > to add it to the Selected Events box.



FYI: Here are the data fields that are specific to this event and are automatically captured.

On the next screen it will display list of Global fields if you would like to capture.  For this example, I am not selecting any of them.

On the following screen you can add any filters you would like.

Just for the hack of it here, I have added  a filter to exclude system databases from the trace.

On the next screen, configure the data storage options. Generally I prefer to store the trace data in file.

Click next and it will bring you to the Summary page.

You can click on Script to generate the script to create event.

Click on Finish to create the event.



You could choose option to start the trace immediately.

If not, right click on the newly created event and select Start Session to start the trace.

Once the trace is started, you can view the Live trace by right clicking the trace and select Watch Live Data.

Or expand the event, select the file, right click and click View Target Data.

Here is sample trace data.

Sample TSQL code to create the extended event trace:

CREATE EVENT SESSION [Monitor Backup Progress] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'Monitor Backup Progress',max_file_size=(10))
WITH
(
   MAX_MEMORY=4096 KB,
   EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
   MAX_DISPATCH_LATENCY=30 SECONDS,
   MAX_EVENT_SIZE=0 KB,
   MEMORY_PARTITION_MODE=NONE,
   TRACK_CAUSALITY=OFF,
   STARTUP_STATE=OFF)
GO

Find identical duplicate indexes

Unlike some other RDBMSs, SQL Server does not stop you from creating duplicate indexes. Often, the developer or DBA adding a new index doesn’t check whether an index with the same key columns and order already exists. This situation is more common than many realize.

Duplicate indexes are not only unnecessary, they are bad. They don’t make any query faster, but they still have to be maintained. Every INSERT, UPDATE, and DELETE has to update all copies of the index, that increases write latency and transaction log usage. They also consume extra disk space and memory, and they lengthen index maintenance and backup operations without providing any additional benefit.

When I started looking for identical duplicate nonclustered indexes, same table, same columns, same order, I wanted something flexible enough to handle a few different scenarios. That’s what this script is for: it helps you spot indexes that are essentially copies of each other so you can decide whether to keep or drop them.

There are two knobs you can tweak:

• If you want to treat indexes as duplicates even when the key columns are in a different order, set @disregard_column_order = 1. Leave it at 0 if you only care about truly identical definitions, including column order.

• By default, the script ignores cases where one index is clustered and the other is nonclustered. If you also want to see those pairs, set @include_clustered_indexes = 1.

---

-- See blog post: https://sqlpal.blogspot.com/2019/05/find-identical-duplicate-indexes-revised.html
SET NOCOUNT ON;
USE [Your Database Name;
/*==========================================================================================
  Script Name : Find_Identical_Duplicate_Indexes.sql
  Purpose:
      Find identical duplicate indexes on the same table (same key columns, same order
      unless configured otherwise), so you can evaluate and potentially drop redundant ones.
  High-level approach:
      - Build a comma-separated list of key columns for every index in the database.
      - Optionally:
          * Include clustered vs nonclustered index pairs.
          * Ignore column order when comparing indexes.
      - Group by (schema, object, index_columns) and return those with COUNT(*) > 1.

  Variables/Parameters:
      @include_clustered_indexes (bit)
          0 = Ignore duplicates where one index is clustered and the other is nonclustered
          1 = Include those cases as duplicates too.

      @disregard_column_order (bit)
          0 = Only consider indexes duplicates if key columns match AND are in the same order.
          1 = Consider indexes duplicates even when the same key columns are in a different order.

  Notes / Caveats:
      - This script does NOT:
          * Consider ASC/DESC sort order differences.
          * Consider included columns.
          * Distinguish filtered vs non-filtered indexes beyond exposing filter_definition.
      - Do NOT blindly drop indexes; review each case in the context of workload and plans.
==========================================================================================*/


/*
    Whether to include identical indexes where one is clustered and 
    the other one is non-clustered
*/
DECLARE @include_clustered_indexes bit = 0;


/*
    Whether to find duplicate indexes where all key columns are the same,
    but not necessarily in the same order. Typical usage:
      - 0 (default): require same column order (more strict, closer to “identical”).
      - 1: ignore key column order, treat any permutation of the same columns as duplicates.
*/
DECLARE @disregard_column_order bit = 0;


;WITH cte AS
(
    SELECT
        o.schema_id,
        o.type_desc,
        o.object_id,
        i.index_id,
        i.name AS index_name,

        /*
            Build a comma-separated list of key column names for this index.
              - We need a stable string representation of the key columns to compare indexes.
              - Ordering of columns in the list is controlled by @disregard_column_order:
                    @disregard_column_order = 0 → order by key_ordinal (true index order)
                    @disregard_column_order = 1 → order by column_id (logical column order)
        */
        index_columns =
            COALESCE(
                STUFF
                (
                    (
                        SELECT
                            CAST(',' + COL_NAME(object_id, column_id) AS varchar(max))
                        FROM sys.index_columns
                        WHERE object_id = i.object_id
                          AND index_id  = i.index_id
                          AND is_included_column = 0          -- Only key columns, not INCLUDE
                        ORDER BY 
                            object_id, 
                            index_id,
                            CASE 
                                WHEN @disregard_column_order = 1 
                                    THEN column_id           -- Ignore index key order
                                ELSE key_ordinal            -- Respect index key order
                            END
                        FOR XML PATH(''), TYPE
                    ).value('.', 'varchar(max)')
                    , 1, 1, ''
                )
            , ''),

        -- Index metadata for review and decision making
        i.type_desc       AS index_type,
        i.is_unique,
        i.data_space_id,
        i.ignore_dup_key,
        i.is_primary_key,
        i.is_unique_constraint,
        i.fill_factor,
        i.is_padded,
        i.is_disabled,
        i.is_hypothetical,
        i.allow_row_locks,
        i.allow_page_locks,
        i.has_filter,
        i.filter_definition

    FROM sys.indexes AS i
    INNER JOIN sys.objects AS o 
        ON o.object_id = i.object_id
    WHERE 
        OBJECTPROPERTY(o.object_id, 'IsMsShipped') = 0   -- Skip system objects
        AND i.index_id <> 0                              -- Skip the heap "index"
        /*
            Control whether clustered indexes participate:

            - If @include_clustered_indexes = 0:
                  i.index_id > 1 → only nonclustered indexes.
            - If @include_clustered_indexes = 1:
                  i.index_id > 0 → clustered + nonclustered.
        */
        AND i.index_id > CASE WHEN @include_clustered_indexes = 1 THEN 0 ELSE 1 END
)

-- Find indexes with identical index_columns on the same object and same type
SELECT
    SCHEMA_NAME(i1.schema_id) AS schema_name,
    i1.type_desc,
    OBJECT_NAME(i1.object_id) AS object_name,
    i1.index_name,
    i1.*  -- Includes index_columns + metadata for review
FROM cte AS i1
INNER JOIN 
(
    /*
        Identify combinations of (schema_id, type_desc, object_id, index_columns)
        that occur more than once → those represent duplicate index definitions.
    */
    SELECT 
        schema_id, 
        type_desc, 
        object_id, 
        index_columns
    FROM cte
    GROUP BY 
        schema_id, 
        type_desc, 
        object_id, 
        index_columns
    HAVING COUNT(*) > 1
) AS i2
    ON  i1.schema_id     = i2.schema_id
    AND i1.type_desc     = i2.type_desc
    AND i1.object_id     = i2.object_id
    AND i1.index_columns = i2.index_columns
ORDER BY 
    schema_name, 
    i1.type_desc, 
    object_name, 
    i1.index_name;

This query does not take the ASC or DESC sort direction into account. You might have two indexes with the same key columns where one is ASC and the other is DESC, and there can be perfectly valid reasons for that. It also doesn’t distinguish whether one or both indexes are filtered. I’d love to hear your feedback before I invest more effort covering every edge case. 

Download the SQL script from GitHub:

Find-Duplicate-SQL-Indexes