SQLDBA- Get the list of indices on table with columns and include columns

-

 

Below help us to get the list of indices  on  table with columns and include columns also.


SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) AS SchemaName,
    QUOTENAME(t.name) AS TableName,
    QUOTENAME(i.name) AS IndexName,
i.type_desc,
    i.is_primary_key,
    i.is_unique,
    i.is_unique_constraint,
    STUFF(REPLACE(REPLACE((
        SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
        FROM sys.index_columns AS ic
        INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
        ORDER BY ic.key_ordinal
        FOR XML PATH
    ), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyColumns,
    STUFF(REPLACE(REPLACE((
        SELECT QUOTENAME(c.name) AS [data()]
        FROM sys.index_columns AS ic
        INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
        ORDER BY ic.index_column_id
        FOR XML PATH
    ), '<row>', ', '), '</row>', ''), 1, 2, '') AS IncludedColumns,
    u.user_seeks,
    u.user_scans,
    u.user_lookups,
    u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0
AND t.name = 'Test'   -- Pass here the table name

/*

Explanation:

sys.indexes: Contains information about all indexes in the database.

sys.index_columns: Links indexes to their columns and indicates whether a column is included (is_included_column = 1).

sys.columns: Provides column names for the table.

OBJECT_ID('YourTableName'): Filters the query to only the specified table.

Replace 'YourTableName' with the name of your table to get the desired results. This query will list all indexes, their columns, and whether each column is an included column.

*/


USE <Db Name>

GO

SELECT 

   i.name AS IndexName,

    i.type_desc AS IndexType,

    c.name AS ColumnName,

    ic.is_included_column AS IsIncludedColumn

    --ic.*

FROM 

    sys.indexes i

INNER JOIN 

    sys.index_columns ic ON i.object_id = ic.object_id AND i.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 

    i.object_id = OBJECT_ID('sls_order_master_extension_closed_order') -- Replace 'YourTableName' with your table name

ORDER BY 

    i.name, ic.is_included_column DESC, ic.index_column_id;


    ----ix_sls_order_master_extension_closed_order_4

    --SELECT TOP(1)* FROM reference.dbo.company_master 


    --SELECT * FROM  sys.index_columns

Comments

Post a Comment

Popular posts from this blog

DBA - Check health and status of Always On or Availability Group using DMVs

-
 -- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys.dm_hadr_cluster SELECT * FROM sys.dm_hadr_cluster_members -- Health of the AGs SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags  INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id -- Health and status of AG replics from the WsFC perspective SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc  INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys.dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys.dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the primary replica.  -- On secondary this wi...
Read more

DBA - Script out the Linked server objects.

-
  Script out the Linked server objects.        CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]     AS       /***********************************************************************************************************    --Purporse: To extract Linkded server connection details      --Created By: kovvuru subbareddy        Purpose:    TEST:        EXEC [dbo].[win_linked_server_objects_extract_select]       ***********************************************************************************************************/     BEGIN   SET NOCOUNT ON      CREATE TABLE #temp_linkedserver_detail   ( ID INTEGER identity(1,1) primary key NOT NULL,   create_linked_server_command VARCHAR(5000),   linked_server_name varchar(100) ...
Read more

SQLDBA_ Failover -Registry Check Pointing a Windows Cluster to Bring SQL Server Online

-
Image
 Issue:  The SQL Server service for our cluster could not be brought online during a recent failover due to registry not found error(s). How can we correct this issue? Sometimes the SQL Server service cannot be brought online when there is a failover to another node in the cluster. When you check the error logs, you may have noticed that the SQL Server service could not be brought online due to certain registry entries.  Here is an example of such error messages: [sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for registry key Software\Microsoft\Microsoft SQL Server\MSSQL10.<InstanceName>\Replication (status 2)). [sqsrvres] Worker Thread (358930): Failed to retrieve the Replication registry key (last error = 0). [sqsrvres] StartResourceService: Failed to start MSSQL$I03 service.  CurrentState: 1 [sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqsrvres] OnlineThread: Error 435 bringing resource online. This issue can...
Read more