Gather & Export SQL Server Instance Metadata to CSV with PowerShell and SMO

As a DBA, you are very likely to maintain an up-to-date inventory of all your SQL Servers. It is hard to imagine any environment where this is not the case. However, keeping that inventory current, whether manually or through automation, is another matter. You also need to capture and track detailed metadata about these SQL Servers, such as:

• What edition and version is each SQL Server instance running?
• How are instance‑level settings configured across all servers?
• Which sp_configure options differ from your standard baseline?

You can get these answers from SSMS or by running T‑SQL on each server, but that becomes painful once you manage more than a handful of instances. A simple PowerShell script can collect the same instance‑level metadata into CSV files that you can open in Excel or load into a reporting tool. This though is not meant to replace detailed configuration management or full CMDB tools, but it gives you a solid, scriptable foundation that you can extend over time.

My goal here is to have a reusable script that is easy to understand, easy to run, and easy to adapt as your environment grows.

---

Why use PowerShell and SMO for metadata?

SQL Server exposes a huge amount of metadata through catalog views, DMVs, and system functions. That’s great when you are writing T‑SQL, but it can be less convenient when you want to:

• Pull the same metadata from many instances.
• Store the results as files for later analysis or auditing.
• Automate the process so it runs on a schedule.

PowerShell gives you a scripting environment that is very good at automation, loops, and working with files. SMO SQL Server Management Objects is a .NET library that exposes SQL Server objects (like servers, databases, tables, and so on) as objects you can work with directly in PowerShell.

To keep the script focused and easy to understand, we will export only instance‑level metadata. That means our script will not touch database‑level or object‑level details like tables or indexes; it stays at the server/instance layer.

The script collects three categories of data:

• Information properties: These come from Server.Information in SMO and include things like version, edition, collation, and operating system information. They give you a high‑level description of the instance.

• Settings: These come from Server.Settings and describe how the instance is configured at a higher level (for example, settings that are not necessarily exposed through sp_configure).

• Configuration (sp_configure‑style): These come from Server.Configuration and map closely to what you see when you run sp_configure. They include both the current run values and the config values that take effect after a restart.

The script also adds a few helpful context columns to every CSV:

• Host name.
• Host IP address (IPv4).
• Number of databases on that instance.
• Server instance name (e.g., ServerA\SQL2019).

This extra information makes it easier to filter and group your data later.

---

Prerequisites and permissions

Before you run the script, make sure you have:

• PowerShell 5.1 or later, or PowerShell 7.x on your admin workstation or jump server.
• The SqlServer PowerShell module installed. This module includes SMO and is the recommended way to access SQL Server from PowerShell. You can install it with:

          Install-Module SqlServer -Scope CurrentUser

• Network connectivity to the SQL Server instance(s) you want to query.
• Enough permissions on SQL Server. In most cases, you’ll want to be a member of the sysadmin role or at least have permissions to view server‑level metadata.

Note that modern SQL Server versions use metadata visibility rules, so if your login does not have the required permissions, some properties may be hidden.

---

The PowerShell script: 

As the script is bit lengthy, you can download it from the GitHub:

SQL Server Metadata Export Script

You can save this script as Export-InstanceMetadata.ps1.

This script keeps the logic straightforward:

• It uses parameters so you can pass in the instance name, output folder, and optional SQL credentials.
• It makes sure the output folder exists.
• It loads the SqlServer module (and fails fast with a clear message if it’s missing).
• It builds wide PSCustomObjects where each property corresponds to a metadata field, and each row corresponds to a specific instance.
• It exports three CSV files, one for information, one for settings, and one for configuration values.

---

Running the script: Basic examples

Once you save the script (for example, as Export-InstanceMetadata.ps1), you can run it in several ways.

Single instance with Windows authentication

If your current Windows account has permissions on the SQL instance, you can simply run:

.\Export-InstanceMetadata.ps1 `
    -ServerInstance "SQL01","SQL02\INST1","SQL03" `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

This will: Connect to SQL Server using Windows authentication.

• Write three CSV files into C:\SQL\Metadata:
• SQLInstanceMetadata_Info.csv
• SQLInstanceMetadata_Settings.csv
• SQLInstanceMetadata_Config.csv
• Show progress messages because we used -Verbose.

Using SQL authentication

If you need to use a SQL login instead of Windows auth, you can pass a credential object:

$cred = Get-Credential  # enter SQL login name and password
.\Export-InstanceMetadata.ps1 `
    -ServerInstance "ProdSql01" `
    -OutputFolder "C:\SQL\Metadata" `
    -SqlCredential $cred `
    -Verbose

The script will then connect using that login and password, while the rest of the logic stays the same.

Scaling out: Multiple instances in a loop

The real power of this approach shows up when you have many instances. You can keep a simple text file of instance names and loop through them.

For example, suppose you have a file C:\SQL\instances.txt with one instance per line:

SQLServer1\SQL2019
ProdSql01
ProdSql02
TestSql0

You can run:

$instances = Get-Content "C:\SQL\instances.txt"
.\Export-InstanceMetadata.ps1 `
    -ServerInstance $instances `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

---

Working with the CSV output

The script produces three “wide” CSV files per instance. That means:

• Every row represents exactly one instance (or one instance + value type in the config file).
• Every column represents a property (for example, Edition, Version, IsClustered, or max server memory (MB)).
• This layout is especially friendly for tools like:
• Excel or Power BI, where you can quickly filter, sort, and build simple charts.
• A central repository or inventory database, where you might append or merge results from multiple runs to track changes over time.

For example, you can:

• Compare max server memory (MB) across all instances to spot outliers.
• Filter by Version to see which servers are still running out of support version of SQL Server.
• Track VersionString to know which instances need patching.

If you prefer a “long” format (one row per property per instance), you can easily transform the CSV later in PowerShell or another tool, but starting with the wide format makes it easy for junior DBAs to explore the data.

When should you use this approach?

This instance‑metadata export is most useful when you want to:

• Build a quick inventory of instance‑level configuration across many servers.
• Capture a snapshot before and after making changes to compare configuration values.
• Feed metadata into another process or review tool without logging in to SSMS for each server.

Powershell script to get list of databases on a server

At one of my clients I received an email from one of the IT Project Managers asking a simple question:

"Can you please let us know which databases reside on the server below, Server1?"

First thought in mind, well from what particular sql instance on that server? It was our general practice to install multiple instances on a server. But at that moment I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc...

But, I kept that thought to myself.

Now, I could launch SSMS, connect to the sql instance and view the list databases displayed in the Object Explorer or, query the sys.databases and get the requested information.

But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere else, like maybe a spreadsheet with database inventory, assuming its up to date.

Instead of that, I decide to launch the Powershell and issue this command:

Get-WmiObject -Query "select * from win32_service `

where PathName like '%%sqlservr.exe%%'" `

-ComputerName "Server1"

Note: To run the above Get-WmiObject, you will need to have the Local Administrator access on the target computer. Good thing is that you can run this remotely and don't need to be logged into the remote server.

It has only one sql instance, great.

Then I issued the following command to grab the databases on that list and send him the results.

Get-SqlDatabase -ServerInstance Server1

OR - To search for a specific pattern in the database name:

Get-SqlDatabase -ServerInstance Server1 | 
Where-Object {$_.Name -like '*report*'}

That last Get-SqlDatabase command requires the SqlServer module loaded, which I already have in my powershell startup.

Import-Module -Name SqlServer

But to add bit more value to this blog, I decided to turn this into a small PowerShell script.

The PowerShell variable $server_name is where you specify the server  name where you would like to look up database names. If that value is not specified, it will use the local computer name.

try
{
    Import-Module -Name SqlServer -ErrorAction Stop

    $server_name   = "Server1" # SERVER/HOST NAME HERE  
    $database_name = "admin"   # NAME OF THE DATABASE YOU WOULD LIKE TO SEARCH OTHERWISE LEAVE THIS BLANK
    $exact_match   = "N"       # WHETHER TO SEARCH FOR AN EXACT DATABASE NAME

    $logfile = "$env:TEMP\logfile_" + (Get-Date).toString("yyyyMMdd_HHmmss") + ".txt"
    
    "Start Time: " + (Get-Date)  | Out-File -Append $logFile
    "Log: $logfile"  | Out-File -Append $logFile
    ""  | Out-File -Append $logFile
    "Server: $server_name" | Out-File -Append $logFile
    "Database: $database_name" | Out-File -Append $logFile
    "Exact Match: $exact_match" | Out-File -Append $logFile
    ""  | Out-File -Append $logFile


    if ($server_name -eq "" -or $server_name -eq $null)
    {
        $server_name = $env:computername
    }

    $sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "$server_name" -ErrorAction Stop

    foreach ($sql_service in $sql_services) 
    {
        $instance_name = $sql_service.Name -replace "MSSQL\$", ""
        if ($sql_service.State -eq "Running")
        {
            $sql_connection = if ($instance_name -eq "MSSQLSERVER") { $sql_service.PSComputerName } else { $sql_service.PSComputerName + "\" + $instance_name }

            if ($database_name -eq "")
            {
                Get-SqlDatabase -ServerInstance $sql_connection | 
                    FT Parent, Name, Owner, ReadOnly, RecoveryModel, Size, Status, UserAccess
            }
            else
            {

                if ($exact_match -eq "Y")
                {
                    Get-SqlDatabase -ServerInstance $sql_connection | Where-Object {$_.name -eq $database_name} | 
                        FT Parent, Name, Owner, ReadOnly, RecoveryModel, Size, Status, UserAccess
                }
                else
                {
                    Get-SqlDatabase -ServerInstance $sql_connection | Where-Object {$_.name -like "*$database_name*"} | 
                        FT Parent, Name, Owner, ReadOnly, RecoveryModel, Size, Status, UserAccess
                }
            }


        }
        else
        {
            "Skipping $sql_connection as it's not running..." | Out-File -Append $logFile
        }
    }

    ""  | Out-File -Append $logFile
    "Completion Time: " + (Get-Date)  | Out-File -Append $logFile
  
}
Catch
{
    $errorMessage = (Get-Date).ToString() + ": Error Occurred - " + $_.Exception.Message
    $errorMessage | Out-File -Append $logFile
    throw
}

"# Launch the notepad.exe to view the log file"
"notepad.exe $logfile"

Note: The script assumes that you are using Windows Authentication to connect to SQL Server and have necessary permissions to query the SQL metadata.

Download this PowerShell script from GitHub at:

Find SQL Databases

 
I have conducted some initial testing on this script, but it has not undergone extensive or thorough testing. Additionally, I have not tested it on SQL servers hosted on Linux platforms. Therefore, I would greatly appreciate your feedback. If you decide to test this script, please feel free to share your thoughts, experiences, and suggestions for enhancements, or point out any errors or issues you encounter.

Powershell script to find SQL Server instances on remote servers

This is regarding finding the SQL Server database services/instances on remote computers, not any other SQL Server components like SSRS, SSIS, Full-Text services etc. maybe installed there...

But, first, if you are just interested in looking up SQL Server services (i.e., database instances) on a single remote computer, you can simply use this PowerShell one-liner:

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" `
              -ComputerName "SQLSERVERVM1" |
              Format-Table -Property PSComputerName, Name, StartMode, State, Status

You can even specify multiple remote computers, each separated by a comma (e.g. "SQLSERVERVM1", "SQLSERVERVM2")

---

This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain, more specifically, new SQL Server  instances added to any existing server or on newly installed servers.  The process is supposed to be for organizations with very large number of computers where you don't want to scan the entire AD everyday to see if any new SQL Servers are installed.

The idea is to:

.

• Get list of all servers in the AD and store the results in to a SQL table
• Get servers with SQL Server installed and store the results into a SQL table
• Compare the list with the previous list and send a report to DBAs

You can find the part 1 of this blog series at the following link:

https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html

Please note that this part does not extend to connecting and discovering SQL Server-specific configuration details such as version, edition, or other instance-level parameters.

---

I will be using the CSV file (new_servers.csv) generated by the PowerShell script mentioned in the above post. You can also create your own text/CSV file with list of servers in it, with Name as the name of the first column, it could be the only column in it.

In below PowerShell script all I am doing is to check if the remote servers have sql server instance Winodws services setup and their current status. I am not checking yet whether I have access to them or what version of sql servers these instances are running. That will be in the next post in this series!

Additionally, in this post I am also inserting the collected information into a sql staging table.

---

What follows are PowerShell and SQL scripts that you can copy/paste, review, analyze and customize to fit your needs.

The PowerShell script to find SQL Server instances on remote servers:

<#


This powershell script uses WMI to connect to the each server and 
check windows services that matches %%sqlservr.exe%% pattern.
Therefore in order for this to work you would need to have access 
to the servers otherwise it will throw Access Denied errors. 
However since I am getting the list of servers to check from a CSV, 
it will continue on to the next server after the errors.


At the end it displays list of servers it successfully connected 
to and a separate list where it errored out.


It also exports the list of sql instances it discovered to a CSV file.

By default it uses the connected users credentials.
Though, there is option ($user variable) to specify a different 
credentials (Windows).  The password field is in plain text so 
I am not a big fan of it.

#>
(Get-Date).ToString() + ": Begin" 
try
{

        $user = ""           # Should be in Domain\UserName format
        $pass = ""
        

        if ($user -eq "") { $user = $Null}


        # If user/pass pair is provided, authenticate it against the domain
        if ($user-ne $Null)
        {
            "Authenticating user $user against AD domain"
            $domain = $user.Split("{\}")[0] 
            $domainObj = "LDAP://" + (Get-ADDomain $domain).DNSRoot 
            $domainObj
            
            $domainBind = New-Object System.DirectoryServices.DirectoryEntry($domainObj,$user,$pass)
            $domainDN = $domainBind.distinguishedName 
            "domain DN: " + $domainDN
            
            # Abort completely if the user authentication failed for some reason
            If ($domainDN -eq $Null) 
               {
                       "Please check the password and ensure the user exists and is enabled in domain: $domain"
                       throw "Error authenticating the user: $user"
                       exit
               }
            else {"The account $user successfully authenticated against the domain: $domain"}

            $passWord = ConvertTo-SecureString -String $pass -AsPlainText -Force
            $credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $passWord
        }

        $csv_file_name = "new_servers.csv"
        $CSVData = Import-CSV $csv_file_name
        $export_file_name = "sql_server_instances.csv"
        $csv_row_count = $CSVData.Count
        (Get-Date).ToString() + ": Total rows in the CSV file: " + $csv_row_count
        $servers = $CSVData.DNSHostName

        $SqlInstancesList = @()
        $ErrorServers = @()
        ""
        $servers
        ""
        
        # iterate through each server and search for sql services on them

        foreach($server in $servers) 

        { 

            ""
            "Searching for SQL Server DB services on: $server"
        try
        {

                If (-Not (Test-Connection -ComputerName $server -Count 2 -Quiet))
                    {Throw "Invalid Computer Name: $server"}

                If ($user-ne $Null)
                   {$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -credential $credentials  -ComputerName $server -ErrorAction Continue}
                Else
                   {$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $server -ErrorAction Continue}
                
                $SqlInstancesList += $SqlServices
                
                "Number of SQL instances found on $server : " + $SqlInstancesList.Count | Write-Host -ForegroundColor Green
        }
        catch
        {
                # even though error occured, it will continue to the next server
                $em = $_.Exception.Message
                "Skipping $server because an error encountered ($em):" | Write-Host -ForegroundColor Yellow
                $ErrorServers += $server + " (" + $em + ")"
               
        }
        } 

        # if there were any errors with any of the servers, print off names of those servers along with the error message/reason
        if ($ErrorServers.Count -gt 0)
        {
                ""
                "Error when looking up SQL Instances on following servers:"  | Write-Host -ForegroundColor Red
                "--------------------------------------------------------"
                $ErrorServers
        }

        ""
        "EXPORTING TO FILE: $export_file_name"
        $SqlInstancesList | select-object -Property PSComputerName, @{n="SqlInstance";e={$_.Name -replace "MSSQL\$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | Export-CSV $export_file_name -NoTypeInformation -Encoding UTF8

        ""
        "SQL Instances Found:" | Write-Host -ForegroundColor Green
        "--------------------"

        Import-Csv -Encoding UTF8 -Path $export_file_name | ft -AutoSize
        (Get-Date).ToString() + ": Complete" 
}
 
Catch
{
    (Get-Date).ToString() + ": Error Occurred" 
     throw  
}

PowerShell script to load the collected information into sql table:

<#


This too uses the connected users credentials to connect to 
sql server instance.


Since I am loading data into a staging table, this first 
truncates that table then loads the data into it.

#>


<# Since the following script doesn't do error checking
   Lets make sure it stops if an error occurs with any
   of the commands. #>

$ErrorActionPreference = 'Stop'
(Get-Date).ToString() + ": Begin Loading data into sql staging table" 

$sql_instance_name = 'SQLVM01\SQL2016AG01' 
$db_name = 'AdminDBA'
$destination_schema_name = 'dbo'
$destination_table_name = 'sql_server_instances_stage'
$export_file_name = 'sql_server_instances.csv'

# Create the destination SQL table if doesn't already exist
$sql_create_table = "
USE [$db_name]
GO
If object_id('$destination_schema_name.$destination_table_name', 'U') is null

CREATE TABLE [$destination_schema_name].[$destination_table_name](
 [id] [int] IDENTITY(1,1) PRIMARY KEY,
 [PSComputerName] [varchar](500) NULL,
 [ServiceName] [varchar](500) NULL,
 [InstanceName] [varchar](500) NULL,
 [PathName] [varchar](1500) NULL,
 [ExitCode] [int] NULL,
 [ProcessID] [int] NULL,
 [StartMode] [varchar](500) NULL,
 [State] [varchar](500) NULL,
 [Status] [varchar](500) NULL,
 [InsertedDate] [datetime] NULL DEFAULT GETDATE()
)
"

"Create destination table:"
"========================="
$sql_create_table
"========================="
""

invoke-sqlcmd -Database $db_name -Query $sql_create_table -serverinstance $sql_instance_name

$truncate_table_command = "truncate table [$destination_schema_name].[$destination_table_name]"
"Truncate table command: " + $truncate_table_command
invoke-sqlcmd -Database $db_name -Query $truncate_table_command -serverinstance $sql_instance_name

$SqlServices = Import-Csv -Encoding UTF8 -Path $export_file_name
foreach ($sqlservice in $SqlServices)
{
                    $PSComputerName      = $SqlService.PSComputerName
                    $Name                = $SqlService.Name
                    $SqlInstance         = $SqlService.SqlInstance
                    $PathName            = $SqlService.PathName
                    $ExitCode            = $SqlService.ExitCode
                    $ProcessID           = $SqlService.ProcessID
                    $StartMode           = $SqlService.StartMode
                    $State               = $SqlService.State
                    $Status              = $SqlService.Status

$insert_query = "INSERT INTO [$destination_schema_name].[$destination_table_name]" + " (PSComputerName,ServiceName, InstanceName,PathName,ExitCode,ProcessID,StartMode,State,Status)
          VALUES ('$PSComputerName','$Name','$SqlInstance','$PathName','$ExitCode','$ProcessID','$StartMode','$State','$Status')" 

"SQL Insert Statement: $insert_query"
$execute_query = invoke-sqlcmd -Database $db_name -Query $insert_query  -serverinstance $sql_instance_name
}

$select_query = "select count(*) rcount from " + $destination_table_name 
$rcount = invoke-sqlcmd -Database $db_name -Query $select_query -serverinstance $sql_instance_name -As DataTables
""
"Number of records inserted into sql table: " + $rcount[0].rcount
(Get-Date).ToString() + ": Complete Loading data into sql staging table" 

SQL Script to create the dbo.sql_server_instances_stage table:

USE [AdminDBA]
GO
if object_id('sql_server_instances_stage', 'U') is not null
drop table [sql_server_instances_stage]
GO

CREATE TABLE [sql_server_instances_stage](
 [id] [int] IDENTITY(1,1) PRIMARY KEY,
 [PSComputerName] [varchar](500) NULL,
 [ServiceName] [varchar](500) NULL,
 [InstanceName] [varchar](500) NULL,
 [PathName] [varchar](1500) NULL,
 [ExitCode] [int] NULL,
 [ProcessID] [int] NULL,
 [StartMode] [varchar](500) NULL,
 [State] [varchar](500) NULL,
 [Status] [varchar](500) NULL,
 [InsertedDate] [datetime] NULL DEFAULT GETDATE()
)

Sample Reports Queries:

-- List All Discovered SQL Server Services by Computer
SELECT 
    PSComputerName,
    ServiceName,
    InstanceName,
    PathName,
    State,
    Status,
    StartMode,
    InsertedDate
FROM sql_server_instances_stage
ORDER BY PSComputerName, ServiceName;

-- Count of SQL Services by State
SELECT 
    State,
    COUNT(*) AS ServiceCount
FROM sql_server_instances_stage
GROUP BY State
ORDER BY ServiceCount DESC;

-- List Services Not Running (State Not 'Running')
SELECT 
    PSComputerName,
    ServiceName,
    InstanceName,
    State,
    Status,
    InsertedDate
FROM sql_server_instances_stage
WHERE State <> 'Running'
ORDER BY PSComputerName, ServiceName;

-- Show Services with Recent Discovery (Last 7 Days)
SELECT 
    PSComputerName,
    ServiceName,
    InstanceName,
    State,
    Status,
    InsertedDate
FROM sql_server_instances_stage
WHERE InsertedDate >= DATEADD(DAY, -7, GETDATE())
ORDER BY InsertedDate DESC;

Find clustered index on non primary key columns

By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index.

That works best in most cases and is the recommended best practice.

And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to.

Scenario:

So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time.

But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now that index may not be optimal. Of course that could be true for any index but the consequences are more severe if that's the case for a clustered index.

Now you are supporting that database in production mode. Users report that the query performance has gotten extremely slow and you also notice that the index optimization job is taking much longer to complete.

As part of your research and troubleshooting this issue, one of things you decide to check is index strategy already in place and you check 1) Are there any missing indexes 2) are the indexes of correct type (unique, clustered, non-clustered etc.), fill factor etc. 3) whether the clustered index is created on right columns etc....

The query that I have below is to find out if clustered index is on non-pk columns. I have consciously decided to exclude tables that have either no clustered index, no primary key or there is clustered as well as non-clustered index created on primary key columns.

-- CREATE A TEST TABLE
IF OBJECT_ID('dbo.tbl_test_ci_on_non_pk', 'U') IS NOT NULL
   DROP TABLE tbl_test_ci_on_non_pk
GO

-- ADD A CLUSTERED INDEX ON A NON-PK COLUMN
CREATE TABLE [dbo].[tbl_test_ci_on_non_pk](
	[id] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
	[name] [varchar](50) NULL)
GO
CREATE CLUSTERED INDEX [idx_ci_tbl_test_ci_on_non_pk_name] 
ON [dbo].[tbl_test_ci_on_non_pk]
([name] ASC)
GO

;WITH cte_indexes

     AS (SELECT db_name()                         db_name, 
                schema_name(o.schema_id)          schema_name, 
                object_name(i.object_id)          object_name, 
                o.type_desc                       object_type, 
                i.NAME                            index_name, 
                i.type_desc                       index_type, 
                i.is_primary_key, 
                o.object_id                       object_id, 

                pk_index_id   = (SELECT index_id FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                pk_index_name = (SELECT name FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                clustered_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ic.index_id = i.index_id) 
        FOR xml path ('')), 1, 1, '') ), ''),

               pk_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ii.is_primary_key = 1) 
        FOR xml path ('')), 1, 1, '') ), '')

         FROM   sys.objects o 
                INNER JOIN sys.indexes i ON o.object_id = i.object_id 
         WHERE  Objectproperty(o.object_id, 'ismsshipped') = 0) 

 
SELECT db_name, 
       schema_name, 
       object_type, 
       object_name,
       index_name  non_pk_clustered_index_name,
       pk_index_name,
       clustered_index_columns,
       pk_index_columns

FROM   cte_indexes 
WHERE  1 = 1 
       AND index_type = 'CLUSTERED' 
       AND pk_index_id ! = 1 
       AND clustered_index_columns != pk_index_columns
ORDER  BY object_name, 
          index_name 

Caveat:  I only considered the traditional index types (clustered, non-clustered, unique, non-unique etc.).

I have tested this on SQL Server versions 2008 R2 and above.

What about orphaned windows users?

SQL Server 2012+ only/: If you're on older versions, this won't work.

Almost every DBA and some developers know that a database user becomes orphaned when its SID doesn't match anything in sys.server_principals. No big deal if you're using contained databases with database-level authentication. But for traditional server logins, those users are locked out of the instance entirely, even though they still "exist" in the database with permissions.

This usually happens when:

• You restore a database to a new server, but the instance-level LOGINS don't match
• SIDs get out of sync between database users and server logins
• Someone deletes a login from sys.server_principals (or Active Directory cleans house)

Microsoft's been dealing with this forever and gave us sp_change_users_login to find/fix it:

How To Troubleshoot Orphaned Users in SQL Server

But sp_change_users_login only works for SQL Server logins, it completely ignores Windows users.

Windows users get even trickier because they often authenticate through Windows group membership. A simple SID comparison between sys.database_principals and sys.server_principals won't catch those. That's where the extended stored procedure xp_logininfo saves the day, it validates actual Windows account existence.

That's why I wrote the T-SQL script below. It finds orphaned Windows users and optionally fixes them by leveraging xp_logininfo to bridge the gap that traditional methods miss.

-- See blog post: https://sqlpal.blogspot.com/2019/06/what-about-orphaned-windows-users.html
/*==========================================================================================
  Script Name : Find-Fix-Orphaned-Windows-Users.sql

  Purpose:
      Identifies and optionally recreates orphaned Windows users in the current database.
      Unlike sp_change_users_login (SQL logins only), this handles Windows users/groups
      using xp_logininfo to validate Windows account existence.

  How it works:
      1. Finds database users (WINDOWS_USER type) with no matching server login SID
      2. Uses xp_logininfo @option='all' to verify if Windows account still exists
      3. Reports orphaned users OR auto-creates missing server logins

  Configuration:
      @fix_orphaned_user (BIT) 
          0 = Report-only mode (default, safe)
          1 = Auto-fix by creating missing server logins

  Prerequisites:
      - Run in target database (orphaned users are DB-scoped)
      - sysadmin or equivalent to create logins (when fixing)
      - Windows Authentication environment

  Safety Notes:
      - xp_logininfo queries Active Directory/local SAM - network dependent
      - Only creates logins, doesn't modify existing database users/permissions
      - Test in non-prod first when @fix_orphaned_user=1
      - Won't fix OS-level account issues (disabled/locked/deleted accounts)

  Typical Scenarios:
      - Database restore to new server (login SIDs don't match)
      - AD cleanup removed accounts referenced by databases
      - Failover cluster with domain trust issues
==========================================================================================*/

DECLARE @username NVARCHAR(500),
        @privilege NVARCHAR(500), 
        @sql NVARCHAR(4000),
        @fix_orphaned_user BIT,
        @cnt INT = 0;

SET @fix_orphaned_user = 0;  -- 0 = Report only (SAFE), 1 = Auto-fix logins

DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
    SELECT dp.NAME 
    FROM sys.database_principals dp 
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid 
    WHERE dp.type_desc = 'WINDOWS_USER' 
      AND dp.authentication_type_desc = 'WINDOWS'
      AND dp.principal_id != 1  -- Exclude dbo
      AND sp.sid IS NULL;       -- No matching server login

OPEN c1;
FETCH c1 INTO @username;

WHILE @@FETCH_STATUS = 0 
BEGIN
    -- Count potential orphans before validation
    SET @cnt = @cnt + 1;

    /*
        xp_logininfo validates Windows account existence in AD/local SAM.
        @option='all' returns privilege level (user/group details).
        NULL result = account doesn't exist = TRUE orphan.
    */
    EXEC xp_logininfo 
        @acctname = @username, 
        @option = 'all', 
        @privilege = @privilege OUTPUT;

    -- Orphan confirmed (no Windows account found)
    IF @privilege IS NULL 
    BEGIN
        RAISERROR('Orphaned Windows user: %s', 10, 1, @username) WITH NOWAIT;
        
        -- AUTO-FIX: Create missing server login
        IF @fix_orphaned_user = 1 
        BEGIN 
            SET @sql = 'CREATE LOGIN [' + @username + 
                       '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + DB_NAME() + ']';
            
            PRINT 'Creating login: ' + @sql;
            EXEC(@sql);
        END
    END;

    FETCH c1 INTO @username;
END;

CLOSE c1;
DEALLOCATE c1;

-- Final status message
IF @cnt = 0 
    RAISERROR('No potential orphaned Windows users found.', 10, 1) WITH NOWAIT;
ELSE IF @cnt > 0 AND @fix_orphaned_user = 0
    RAISERROR('%d potential orphaned Windows users (run with @fix_orphaned_user=1 to auto-fix).', 
              10, 1, @cnt) WITH NOWAIT;

Report only:

Report and fix:

Download Script: Find-Orphan-Windows-Users-In-SQLServer

Caveat: The script won't fix OS-level account issues (deleted/disabled/locked Windows users).

Linux: Untested but should work, let me know!