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.

/*

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;