Monday, May 24, 2021

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 AS [schema],
t.object_id AS [table_object_id], AS [table_name],
c.column_id, AS [column_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;


Related Article:

No comments:

Post a Comment