Check all indexes in all table in Database in MS SQL Server

 USE YourDatabaseName; -- Replace with your actual database name

-- Query to retrieve index information
SELECT 
    TableName = t.name,
    IndexName = ind.name,
    IndexType = CASE ind.index_id 
                    WHEN 0 THEN 'Heap (No Clustered Index)'
                    WHEN 1 THEN 'Clustered'
                    ELSE 'Nonclustered'
                END,
    ColumnName = col.name,
    ColumnPosition = ic.key_ordinal
FROM 
    sys.indexes ind
INNER JOIN 
    sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN 
    sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN 
    sys.tables t ON ind.object_id = t.object_id
WHERE 
    ind.type_desc <> 'Heap' -- Exclude heap tables (tables without clustered indexes)
ORDER BY 
    t.name, ind.name, ic.key_ordinal;

Explanation:

1. USE YourDatabaseName: Replace YourDatabaseName with the name of your SQL Server database to switch to that database context.

2. sys.indexes: This system view contains information about indexes in the database.

3. sys.index_columns: This view provides details about the columns that are part of each index.

4. sys.columns: This view gives information about columns in tables.

5. sys.tables: This view contains information about tables in the database.

6. SELECT: Retrieves the table name (t.name), index name (ind.name), index type (ind.index_id), column name (col.name), and column position (ic.key_ordinal).

7. CASE statement: Checks the ind.index_id to determine the type of index (clustered, nonclustered, or heap).

8. WHERE: Filters out heap tables (ind.type_desc <> 'Heap') because they don't have traditional indexes.

9. ORDER BY: Orders the results by table name (t.name), index name (ind.name), and column position (ic.key_ordinal).

Notes:

• Make sure to replace YourDatabaseName with the actual name of your database.
• This query provides comprehensive information about all indexes in the database except for heap tables.
• Running this query requires appropriate permissions to access the system views (sys.indexes, sys.index_columns, sys.columns, sys.tables).

By executing this query in SQL Server Management Studio (SSMS) or any other SQL query tool, you can obtain a detailed list of indexes and their configurations across all tables in your database.

Recursive Join in SQL

 A recursive query is a powerful feature that allows us to query hierarchical data which are used in relational databases. They are a compound operation that helps in accumulating records every time until a repetition makes no change to the result. 

Recursive queries are useful to build a hierarchy tree, best in working with hierarchical data such as org charts for the bill of materials traverse graphs or to generate arbitrary row sets. This involves joining a set with itself an arbitrary number of times. A recursive query is usually defined by the anchor part and the recursive part.

Recursive joins are sometimes also called “fixed-point joins”. They are used to obtain the parent-child data. In SQL Recursive joins are implemented with recursive common table expressions. Recursive common table expression (CTEs) is a  way to reference a query over and over again. 

Now we understand the Recursive Join in SQL by using an example.

Step 1: First we create a database of employees, Where  Common Table Expression of the company for its Employee Id, Employee name, Employee age.

Query:

CREATE TABLE employees (
 id serial,
 name varchar(20),
  age int
);

Step 2: In this step insert values into an employee table.
Query:
INSERT INTO employees VALUES (1, 'Ankit', 32);
INSERT INTO employees VALUES (2, 'Ayush', 31);
INSERT INTO employees VALUES (3, 'Piyush', 42);
INSERT INTO employees VALUES (4, 'Ramesh', 31);
INSERT INTO employees VALUES (5, 'Rohan', 29);
INSERT INTO employees VALUES (6, 'Harry', 28);
INSERT INTO employees VALUES (7, 'Rohit', 32);
INSERT INTO employees VALUES (8, 'Gogi', 32);
INSERT INTO employees VALUES (9, 'Tapu', 33);
INSERT INTO employees VALUES (10, 'Sonu', 40);
Step 3: A statement that gives all the reports that roll up into a certain organization within the company. A CTE is defined using a WITH statement, followed by a table expression definition. The AS command is used to rename a column or table with an alias. A recursive CTE must contain a UNION  statement and be recursive.
Query:WITH RECURSIVE managertree AS (
 SELECT id, name, age
 FROM employees
 WHERE id = 1 
 UNION ALL
 SELECT e.id, e.name, e.age
 FROM employees e
 INNER JOIN managertree mtree ON mtree.id = e.id
)
Step 4: To check the recursive join data we use the following query.
Query:
SELECT * FROM managertree;
Output: