Become a SQL Scientist: Explore Every Layer of Table Metadata in SQL Server

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable';

SELECT
    ind.name AS IndexName,
    ind.type_desc AS IndexType,
    col.name AS ColumnName,
    ic.is_included_column,
    ind.is_unique,
    ind.is_primary_key
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
WHERE ind.object_id = OBJECT_ID('YourTable');

SELECT
    kc.name AS ConstraintName,
    c.name AS ColumnName,
    kc.type_desc
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE kc.parent_object_id = OBJECT_ID('YourTable');

EXEC sp_helpconstraint 'YourTable';

SELECT name, is_disabled, create_date
FROM sys.triggers
WHERE parent_id = OBJECT_ID('YourTable');

SELECT *
FROM sys.stats
WHERE object_id = OBJECT_ID('YourTable');

SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities ('dbo.YourTable', 'OBJECT');

SELECT referenced_schema_name, referenced_entity_name, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('dbo.YourTable', 'OBJECT');

SELECT SUM(row_count) AS ApproxRowCount
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('YourTable') AND index_id IN (0,1);

SELECT t.name AS TableName,
       i.name AS IndexName,
       f.name AS Filegroup
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
WHERE t.name = 'YourTable';

SELECT
    princ.name AS Principal,
    perm.permission_name,
    perm.state_desc
FROM sys.database_permissions perm
JOIN sys.objects obj ON perm.major_id = obj.object_id
JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id
WHERE obj.name = 'YourTable';

SELECT
    ps.partition_number,
    ps.row_count,
    ps.data_compression_desc
FROM sys.partitions ps
WHERE ps.object_id = OBJECT_ID('YourTable');

EXEC sp_AnalyzeTableMetadata @TableName = 'YourTable';