I'm wondering what the simplest way to list all indexes for all tables in a database is.
Should I call sp_helpindex for each table and store the results in a temp table, or is there an easier way?
Can anyone explain why constraints are stored in sysobjects but indexes are not?
Here's an example of the kind of query you need:
select
i.name as IndexName,
o.name as TableName,
ic.key_ordinal as ColumnOrder,
ic.is_included_column as IsIncluded,
co.[name] as ColumnName
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id
and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id
and co.column_id = ic.column_id
where i.[type] = 2
and i.is_unique = 0
and i.is_primary_key = 0
and o.[type] = 'U'
--and ic.is_included_column = 0
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
;
This one is somewhat specific to a certain purpose (I use it in a little C# app to find duplicate indexes and format the output so it's actually readable by a human). But you could easily adapt it to your needs.
You could reference sysindexes
Another trick is to look at the text of sp_helpindex to see how it reconstructs information from the underlying tables.
sp_helptext 'sp_helpindex'
I don't have a reference for this, but I believe constraints are not stored in sysobjects because they are a different kind of thing; sysindexes contains meta-data about objects in sysobjects.
If you need more information, here is a nice SQL script, which I use from time to time:
DECLARE @TabName varchar(100)
CREATE TABLE #temp (
TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200),
IndexKeys varchar(200), IndexSize int
)
DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sysobjects WHERE xtype = 'U'
OPEN cur
FETCH NEXT FROM cur INTO @TabName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
EXEC sp_helpindex @TabName
UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL
FETCH NEXT FROM cur INTO @TabName
END
CLOSE cur
DEALLOCATE cur
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'
UPDATE #temp SET IndexSize =
((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name
SELECT * FROM #temp
ORDER BY TabName, IndexName
DROP TABLE #temp
Here is a script that will return SQL statements to recreate all the indexes in a database.
SELECT ' CREATE ' +
CASE
WHEN I.is_unique = 1 THEN ' UNIQUE '
ELSE ''
END +
I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
I.name + ' ON ' +
SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
KeyColumns + ' ) ' +
ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +
CASE
WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
ELSE ' PAD_INDEX = OFF '
END + ',' +
'FILLFACTOR = ' + CONVERT(
CHAR(5),
CASE
WHEN I.fill_factor = 0 THEN 100
ELSE I.fill_factor
END
) + ',' +
-- default value
'SORT_IN_TEMPDB = OFF ' + ',' +
CASE
WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
ELSE ' IGNORE_DUP_KEY = OFF '
END + ',' +
CASE
WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
ELSE ' STATISTICS_NORECOMPUTE = ON '
END + ',' +
' ONLINE = OFF ' + ',' +
CASE
WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
ELSE ' ALLOW_ROW_LOCKS = OFF '
END + ',' +
CASE
WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
ELSE ' ALLOW_PAGE_LOCKS = OFF '
END + ' ) ON [' +
DS.name + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM sys.indexes I
JOIN sys.tables T
ON T.object_id = I.object_id
JOIN sys.sysindexes SI
ON I.object_id = SI.id
AND I.index_id = SI.indid
JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name + CASE
WHEN MAX(CONVERT(INT, IC1.is_descending_key))
= 1 THEN
' DESC '
ELSE
' ASC '
END
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
ORDER BY
MAX(IC1.key_ordinal)
FOR XML PATH('')
),
1,
2,
''
) KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp3
)tmp4
ON I.object_id = tmp4.object_id
AND I.Index_id = tmp4.index_id
JOIN sys.stats ST
ON ST.object_id = I.object_id
AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG
ON I.data_space_id = FG.data_space_id
LEFT JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
FOR XML PATH('')
),
1,
2,
''
) IncludedColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp1
WHERE IncludedColumns IS NOT NULL
) tmp2
ON tmp2.object_id = I.object_id
AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0
--AND T.name NOT LIKE 'mt_%'
--AND I.name NOT LIKE 'mt_%'
--AND I.Object_id = object_id('Person.Address') --Comment for all tables
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes
I do not have a clear explanation why indexes are not stored in sys.objects. But I would like to contribute to find a simple way to list all indexes for all tables and views in a database. The following query retrieves all indexes including their type and also their object id and object type.
use /*Enter here your database*/
go
select A.Object_id,B.name,B.type,B.type_desc, A.index_id,A.type,A.type_desc
from sys.indexes A left join sys.objects B on A.object_id=B.object_id
where B.type = 'U' or B.type='V' /*filtering on U or V to retrieve tables and views only*/
order by B.name ASC /*Optional sorting*/
I've written this code to iterate through all the databases in your server and push it to a table in a database named Maintenance. You should create this database first and then create a table in that database with the following fields:
CREATE TABLE [dbo].[DBCC_Stats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](50) NULL,
[SchemaName] [nvarchar](128) NULL,
[TableName] [sysname] NOT NULL,
[StatName] [nvarchar](128) NULL,
[modification_counter] [bigint] NULL,
[rows] [bigint] NULL,
[rows_sampled] [bigint] NULL,
[% Rows Sampled] [bigint] NULL,
[last_updated] [datetime2](7) NULL,
[DateEntered] [datetime] NULL,
CONSTRAINT [PK_DBCC_Stats] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DBCC_Stats] ADD CONSTRAINT [DF_DBCC_Stats_DateEntered] DEFAULT (getdate()) FOR [DateEntered]
To use the stored procedure below you'd pass in the server name. usp_Execute_Stats '[YourServerName]'
`CREATE PROCEDURE usp_Execute_Stats
@ServerName varchar(100)
AS
BEGIN
DECLARE @strSQL varchar(max)
SET @strSQL='USE ?
SELECT ''' + '?' + ''' AS DatabaseName,OBJECT_SCHEMA_NAME(obj.object_id) SchemaName, obj.name TableName,
stat.name StatName, modification_counter,
[rows], rows_sampled, rows_sampled* 100 / [rows] AS [% Rows Sampled],
last_updated
FROM ' + @ServerName + '.' + '?' + '.sys.objects AS obj
INNER JOIN ' + @ServerName + '.' + '?' + '.sys.stats AS stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.is_ms_shipped = 0
ORDER BY modification_counter DESC'
INSERT INTO Maintenance.dbo.vwDBCC_Stats
EXEC sp_MSforeachdb @strSQL
--Delete older logs
DELETE Maintenance.dbo.DBCC_Stats
--WHERE DatabaseName IN('Master','Model','MSDB','TempDB')
WHERE [DateEntered] < getdate()-14
END`
