Home > Documentation > SQL Server

Useful SQL Server Queries

This article describes some not so popular queries to get useful information about SQL Server database and its elements.

Table sizes. Get sizes of all database tables ordering from large to small:

select schema_name(tab.schema_id) + '.' + tab.name as table_name, 
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
    inner join sys.indexes ind 
        on tab.object_id = ind.object_id
    inner join sys.partitions part 
        on ind.object_id = part.object_id and ind.index_id = part.index_id
    inner join sys.allocation_units spc
        on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc

Tables without Primary Key. Sometime it is required to get all tables having no primary key defined for optimization. Here is the statement for this purpose:

select schema_name(tab.schema_id) + '.' + tab.[name] as table_name
from sys.tables tab left outer join sys.indexes pk
    on tab.object_id = pk.object_id and pk.is_primary_key = 1
where pk.object_id is null
order by table_name

Recently created tables. Find all tables belong to the current SQL Server database and created in last 30 days:

select schema_name(schema_id) + '.' + name as table_name,
       create_date
from sys.tables
where create_date > DATEADD(DAY, -30, CURRENT_TIMESTAMP)
order by create_date desc

Recently modified tables. Find all tables belong to the current SQL Server database and modified in last 30 days:

select schema_name(schema_id) + '.' + name as table_name,
       modify_date
from sys.tables
where modify_date > DATEADD(DAY, -30, CURRENT_TIMESTAMP)
order by modify_date desc

Foreign Keys. Find all foreign keys belong to the current SQL Server database:

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
    fk.name as fk_name
from sys.foreign_keys fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables pk_tab
        on pk_tab.object_id = fk.referenced_object_id
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name

Have questions? Contact us