--Table and Index Profile

--List All Tables
select *
From INFORMATION_SCHEMA.TABLES
Where TABLE_TYPE = 'BASE TABLE'
Order by Table_Schema, Table_name

--List of Tables and Indexes
select  T.object_id 
  ,I.index_id
  --,P.partition_id
  ,SCHEMA_NAME(T.schema_id) as SchemaName
  ,T.name As TableName
  ,P.partition_number
  ,I.name as IndexName
  ,I.type_desc as IndexType
  ,is_primary_key
  ,is_unique
  ,is_unique_constraint
  ,fill_factor
From sys.tables T
Left Outer Join sys.indexes I
 on I.object_id = T.object_id
Left Outer Join sys.partitions P
 On P.object_id = T.object_id
 And P.index_id = I.index_id
Where T.type = 'U'
Order by  SCHEMA_NAME(T.schema_id), T.name, P.partition_number, I.index_id

--List of Heap Tables
select  SCHEMA_NAME(T.schema_id) as SchemaName
  ,T.name As TableName
  ,I.name as IndexName
  ,I.type_desc as IndexType
From sys.tables T
Inner Join sys.indexes I
 on T.object_id = I.object_id
 and T.type = 'U'
 and I.type = 0   --Heap
Order by  SCHEMA_NAME(T.schema_id), T.name, Index_ID

--List of Partitioned Indexes
select  T.object_id 
  ,I.index_id
  ,SCHEMA_NAME(T.schema_id) as SchemaName
  ,T.name As TableName
  ,P.partition_number
  ,I.name as IndexName
  ,I.type_desc as IndexType
  ,is_primary_key
  ,is_unique
  ,is_unique_constraint
  ,fill_factor
From sys.tables T
Inner Join sys.indexes I
 on I.object_id = T.object_id
Inner Join sys.partitions P
 On P.object_id = T.object_id
 And P.index_id = I.index_id
Where T.type = 'U'
And  P.partition_number > 1 
Order by  SCHEMA_NAME(T.schema_id), T.name, P.partition_number, I.index_id

--List of Tables with most indexes
select  top 10
   T.object_id 
  ,SCHEMA_NAME(T.schema_id) as SchemaName
  ,T.name As TableName
  ,count(1)
From sys.tables T
Inner Join sys.indexes I
 on I.object_id = T.object_id
Inner Join sys.partitions P
 On P.object_id = T.object_id
 And P.index_id = I.index_id
Where T.type = 'U'
Group by T.object_id,SCHEMA_NAME(T.schema_id),T.name
Order by count(1) desc

--List of Tables without a Unique Index
select  T.object_id 
  ,SCHEMA_NAME(T.schema_id) as SchemaName
  ,T.name As TableName
From sys.tables T
Where Not Exists (select 1 from sys.indexes where object_id = T.object_id and is_unique = 1)

--List of Tables without an index
select  T.object_id 
  ,SCHEMA_NAME(T.schema_id) as SchemaName
  ,T.name As TableName
From sys.tables T
Where Not Exists (select * from sys.indexes where object_id = T.object_id and index_id 0)

--Missing Indexes
select * from sys.dm_db_missing_index_groups
select * from sys.dm_db_missing_index_details
select * from sys.dm_db_missing_index_group_stats
select * from sys.dm_db_missing_index_columns (@index_handle)

--Unused Indexes Note: Some index may show up on this query depends on the usage
Select row_number() Over (order by SCHEMA_NAME(O.schema_id),object_name(IUS.object_id),I.name) as UnusedIndex
 ,SCHEMA_NAME(O.schema_id) as SchemaName
 ,object_name(IUS.object_id) as TableName
 ,I.name as IndexName
 ,I.index_id
From sys.dm_db_index_usage_stats IUS
Inner Join sys.indexes I
 on IUS.object_id = I.object_id And I.index_id = IUS.index_id
Inner Join sys.objects O
 on I.object_id = O.object_id
Inner Join sys.partitions P
 on P.object_id = IUS.object_id And P.index_id = IUS.index_id
Where IUS.database_id = DB_ID()
and OBJECTPROPERTY(IUS.object_id, 'IsUserTable') = 1
and I.type_desc = 'nonclustered'
and I.is_primary_key = 0
and I.is_unique_constraint = 0
and P.rows > 1000

--Index Fragmentation
DBCC SHOWCONTIG

--Look at Fragmented indexes on DMV for all tables in all databases
SELECT
      db.name AS databaseName
    , ps.OBJECT_ID AS objectID
    , ps.index_id AS indexID
    , ps.partition_number AS partitionNumber
    , ps.avg_fragmentation_in_percent AS fragmentation
    , ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
    ON db.database_id = ps.database_id
WHERE ps.index_id > 0
AND  ps.page_count > 100
AND  ps.avg_fragmentation_in_percent > 20 --fragmentation up to 15% is typically considered acceptable

--Fix (need to create the SP first)
/*
Exec dbo.uspIndexDefrag
          @executeSQL           = 1
        , @minFragmentation     = 80
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1
        , @database             = 'AdventureWorks'
        , @tableName            = 'AdventureWorks.Sales.SalesOrderDetail';

*/