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

SQL DBA - Find the no of databases base, no of tables each database and no of column each table

We got requirement  how many no of database in each instance ,  how many  no of tables each database and how many  no of columns in each table. 

Below queries  useful to get the above information. 

1. Below queries will return with multiple result set  with above information. 

--Number of Databases in the Cluster (Production Only)

SELECT COUNT(*) AS NumberOfDatabases

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' );  -- Adjust this filter based on your naming convention

--SELECT * FROM sys.databases

--2. Number of Tables Within Each Database

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

SELECT ''' + name + ''' AS DatabaseName, COUNT(*) AS TableCount

FROM sys.tables;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;

--3. Number of Columns Within Each Table

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

SELECT ''' + name + ''' AS DatabaseName, 

       t.name AS TableName, 

       COUNT(c.column_id) AS ColumnCount

FROM sys.tables t

JOIN sys.columns c ON t.object_id = c.object_id

GROUP BY t.name;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;

2. Below Query  will give the same information in single result set. 

DECLARE @sql NVARCHAR(MAX) = '';

-- Create temp table to store results

SET @sql = '

IF OBJECT_ID(''tempdb..#DBTableColumnInfo'') IS NOT NULL DROP TABLE #DBTableColumnInfo;

CREATE TABLE #DBTableColumnInfo (

    DatabaseName SYSNAME,

    TableName SYSNAME,

    ColumnCount INT

);';

-- Append dynamic SQL for each database

SELECT @sql += '

USE [' + name + '];

INSERT INTO #DBTableColumnInfo (DatabaseName, TableName, ColumnCount)

SELECT 

    ''' + name + ''' AS DatabaseName,

    t.name AS TableName,

    COUNT(c.column_id) AS ColumnCount

FROM sys.tables t

JOIN sys.columns c ON t.object_id = c.object_id

GROUP BY t.name;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

-- Final select

SET @sql += '

SELECT 

    DatabaseName,

    COUNT(DISTINCT TableName) AS TableCount,

    SUM(ColumnCount) AS TotalColumns

FROM #DBTableColumnInfo

GROUP BY DatabaseName;

SELECT * FROM #DBTableColumnInfo;

SELECT COUNT(DISTINCT DatabaseName) AS TotalProductionDatabases FROM #DBTableColumnInfo;

';

EXEC sp_executesql @sql;