---

1. List All Tables in the Current Database

When to Use:
To list all user-defined tables in the current database.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

---

2. Get All Columns and Metadata of a Table

When to Use:
To review column names, data types, and constraints in a given table.

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

---

3. Find Primary Key Columns

When to Use:
To identify which columns make up the primary key of a table.

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
  AND TABLE_NAME = 'YourTableName';

---

4. Find Foreign Key Relationships

When to Use:
To discover how your tables are connected via foreign key constraints.

SELECT 
    fk.name AS FK_Name,
    tp.name AS SourceTable,
    cp.name AS SourceColumn,
    tr.name AS TargetTable,
    cr.name AS TargetColumn
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id;

---

5. List All Views and Their Definitions

When to Use:
To inspect existing views and understand their underlying SQL.

SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS;

---

6. Show Indexes and Their Columns

When to Use:
To analyze which indexes exist on which columns in a table.

SELECT 
    t.name AS TableName,
    ind.name AS IndexName,
    col.name AS ColumnName,
    ind.is_primary_key,
    ind.is_unique
FROM sys.indexes ind 
JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id 
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id 
JOIN sys.tables t ON ind.object_id = t.object_id
WHERE t.is_ms_shipped = 0;

---

7. Detect Columns with Default Values

When to Use:
To see which columns have default values applied via constraints.

SELECT c.name AS ColumnName, d.definition AS DefaultValue
FROM sys.columns c
LEFT JOIN sys.default_constraints d
  ON c.default_object_id = d.object_id
WHERE c.object_id = OBJECT_ID('YourTableName');

---

8. Count Rows in a Table (Estimate or Actual)

When to Use:
To estimate or directly count rows in a table.

Estimated Count:

SELECT SUM(row_count) AS EstimatedRows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('YourTableName')
  AND (index_id = 0 OR index_id = 1);

Actual Count:

SELECT COUNT(*) AS ActualRows FROM YourTableName;

---

9. Identify Nullable vs Non-Nullable Columns

When to Use:
To understand which fields require a value and which are optional.

SELECT COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName';

---

10. Search for a Column Name Across All Tables

When to Use:
To find where a specific column is used in the database.

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%YourColumnName%';