Skip to content

## 📝 Author

Birat Aryal — birataryal.github.io
Created Date: 2025-06-18
Updated Date: Tuesday 17th June 2025 21:41:17
Website - birataryal.com.np
Repository - Birat Aryal
LinkedIn - Birat Aryal
DevSecOps Engineer | System Engineer | Cyber Security Analyst | Network Engineer

📘 SQL Server and Table Insights

This document provides a set of SQL queries to explore and audit database objects in a Microsoft SQL Server environment. These queries help verify table sizes, row counts, indexes, procedures, and functions — useful for database health checks, performance tuning, and replication diagnostics.

📋 1. Database Details

This query returns all databases present in the server with its creation date.

SQL
SELECT name, database_id, create_date  

FROM sys.databases;  

GO

📋 2. Database Logical & Physical File Details with Size

This query returns the logical and physical file names, file type (Data/Log), and file size in MB for all databases in the SQL Server instance.

SQL
SELECT
    db.name AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.type_desc AS FileType,
    mf.physical_name AS PhysicalFileName,
    CAST((mf.size * 8.0) / 1024 AS DECIMAL(10,2)) AS SizeMB
FROM 
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id
ORDER BY 
    db.name, mf.type_desc;

Note

If Wanted to get the details of the specific database only then add WHERE db.name = 'My_Database'

📋 3. Verify Table Names and Row Counts

This query returns all user tables with their corresponding row counts. It excludes system-generated or temporary tables like dt%.

SQL
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.NAME NOT LIKE 'dt%'
    AND i.index_id <= 1
ORDER BY 
    t.name ASC;

📦 4. Get Table Size in MB

This query provides a breakdown of total, used, and unused space (in MB) per table.

SQL
SELECT
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.rows AS NumRows,
    CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,
    CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB
FROM
    sys.tables t
    JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.name NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.object_id > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    Total_MB DESC, t.Name;

Note

If needed to look over specific table then include AND t.name = '<Table Name>' on WHERE condition.

⚙️ 5. List All Stored Procedures

Returns all stored procedures in the database, ordered alphabetically.

SQL
SELECT * 
FROM sys.procedures 
ORDER BY name ASC;

🧮 6. List All User-Defined Functions

Displays scalar (FN, FS), inline table-valued (IF), and multi-statement table-valued (TF) functions with definitions.

SQL
SELECT 
    o.name AS FunctionName,
    o.type_desc AS ObjectType,
    m.definition AS FunctionDefinition
FROM 
    sys.objects o
JOIN 
    sys.sql_modules m ON o.object_id = m.object_id
WHERE 
    o.type IN ('FN', 'IF', 'TF', 'FS', 'FT')
ORDER BY 
    o.name;

🗃 7. List All Indexes

Displays index metadata, including uniqueness, primary key status, and whether the index is disabled.

SQL
SELECT 
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey,
    i.is_unique_constraint AS IsUniqueConstraint,
    i.is_disabled AS IsDisabled
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    t.NAME NOT LIKE 'dt%'
ORDER BY 
    TableName,
    IndexName;

🔐 Notes and Best Practices

  • Most queries exclude system tables (dt%) and system objects (is_ms_shipped = 0).
  • Run these queries in SSMS (SQL Server Management Studio) or any T-SQL-compatible client.
  • Adjust WHERE conditions if you have a different naming convention or wish to include system tables.

Tip

  • Before configuring replication or migrating tables.
  • During performance tuning to find large tables or missing indexes.
  • For regular audits and reporting of database object metadata.

📚 Additional Reading