A few handy SQL scripts for SQL Server users

From time to time, I have to take a look at the Microsoft SQL Server to check if everything is fine. In some cases, I have some housekeeping to do. Here is the list of useful scripts I use frequently to perform basic tasks.

Check database tables and their sizes

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows, 
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB 
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 
  INNER 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 TotalSpaceMB DESC, t.Name

Sometimes it is handy to check if some of the database tables are not too large. It is common, that tracking or logging tables got wild and take up a lot of space. In such a case, you can consider trimming them.

Are there any indexes?

Once you have your tables trimmed, you should check if there are indexes created on these tables. The reason to create indexes and how to choose which columns to include is a huge topic on its own. I will not try to explain it here. But here you have a query:

SELECT schema_name(t.schema_id) + '.' + t.[name] as table_or_view_name, 
    i.[name] as index_name,
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type,
    case when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [unique],
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type]
FROM sys.objects t
  INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
WHERE schema_name(t.schema_id) NOT IN ('sys')

Isn’t it too big?

If you are not sure where the database files are located and what are their sizes, you can use this one:

SELECT
    name, size, size * 8/1024 'Size (MB)',
    max_size, database_id, type_desc, physical_name, 
    state_desc, is_media_read_only, is_read_only, 
    is_sparse, is_percent_growth, is_name_reserved, 
    is_persistent_log_buffer
FROM sys.master_files

Oh, I need to truncate these files!

Attention! The query below should be used once your database is backed up. The logs will be truncated and you will lose recovery data for the transactions stored in these logs.

The query below is taking all log files and trying to truncate them. The system databases are skipped.

DECLARE @ScriptToExecute VARCHAR(MAX);

SET @ScriptToExecute = '';

SELECT
@ScriptToExecute = @ScriptToExecute +
'USE ['+ d.name +']; alter database ['+ d.name +'] set recovery simple; alter database ['+ d.name +'] set recovery full; CHECKPOINT; DBCC SHRINKFILE (['+f.name+']);'
FROM sys.master_files f
  INNER JOIN sys.databases d 
    ON d.database_id = f.database_id
WHERE f.type = 1 
  AND d.database_id > 4 
  AND d.state_desc = 'ONLINE'

SELECT @ScriptToExecute ScriptToExecute

EXEC (@ScriptToExecute)