Metadata Information from SYS tables – SQL Server

The underlying tables which store the metadata for a certain database are called system tables. A system table called sys tables is used to keep track of data about the tables in a database. A record is created in the sys for each table or object that is added to the database.

These system tables belong to a unique class of tables that the SQL Server Engine uses to hold data on the configurations and objects of SQL Server instances that may be accessed via system views.

The following query retrieves information about schemas, tables, its columns, the indexes that were created on these tables.

SELECT
s.name AS [schema],
t.object_id AS [tableObjectId],
t.name AS [TableName],
c.column_id,
c.name AS [ColumnName],
i.name AS [IndexName],
i.type_desc AS [IndexType]
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;