How to list all indexes in SQL Server

I create a script that list all indexes in SQL Server. At the same time I want to show you some SQL Server script properties. I used sp_MSforeachtable stored procs to list indexes. The below script shows us all indexes, with columns and table names.

drop table if exists ##tmpTablesAndIndexes
create table ##tmpTablesAndIndexes
(
id            int identity(1,1),
tname         varchar(100) null,
index_name    varchar(250) null,
index_descr   varchar(250) null,
index_columns varchar(250) null
)
EXEC sp_MSforeachtable @command1='declare @tname varchar(100)
SELECT @tname=''?''
insert into#tmpTablesAndIndexes (index_name,index_descr,index_columns)
exec sp_helpindex ''?''
update ##tmpTablesAndIndexes
set tname=@tname
where tname is null
'
select tname,index_name,index_descr,index_columns
from ##tmpTablesAndIndexes
order by tname asc

Let’s analyse script:

Before SQL Server 2016 if you want to drop existing object you use this type of script:

IF OBJECT_ID('dbo.Product, 'U') IS NOT NULL  DROP TABLE dbo.Product;

But after version 2016 you can use drop if exists. For example:

DROP TABLE IF EXISTS dbo.Product

What is ## symbols before table name?

It means global temp table. Temporary tables are shown with single # symbol before table name. Temporary tables are valid during the current session. But global temp tables are accessible from the other sessions until the main session closes. Main session means time that the table is created.

What is sp_MSforeachtable ?

sp_MSforeachtable is a stored procedure that is used to apply a T-SQL command to every table, iteratively in the current database. ‘?’ means table name.

Some samples:

--- display all table names in a database
exec sp_MSforeachtable @command1='select ''?'''
---  display the size of all tables in a database
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
--- rebuild all indexes of all tables in a database with fillfactor 80
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"