Get all schemas, tables, columns and indexes in SQL Server

 Use the below sql script to find out the list of schemas, tables, columns and indexes in a database.

SQL Code:

SELECT s.name AS [schema],
t.object_id AS [table_object_id],
t.name AS [table_name],
c.column_id,
c.name AS [column_name],
i.name AS [index_name],
i.type_desc AS [index_type]
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c
ON t.object_id = c.object_id
LEFT JOIN sys.index_columns AS ic
ON c.object_id = ic.object_id and c.column_id = ic.column_id
LEFT JOIN sys.indexes AS i
ON ic.object_id = i.object_id and ic.index_id = i.index_id
ORDER BY [schema], [table_name], c.column_id;

Output:

Related Article:

• Retrieve information on backup and restore operations in SQL Server
• Return list of SQL Agent jobs with schedule information in SQL Server
• Count the Number of Tables in a Database in SQL Server
• Retrieve List of all Stored Procedures in SQL Server
• Display all data files for all databases with size and growth info in SQL Server
• Get all schemas, tables, columns and indexes in SQL Server
• Retrieve all Table detail from Known Column in SQL Server
• Get the list of all databases on a server in SQL Server
• Show Size of All Tables in Current Database in SQL Server