Find out when your SQL Server databases were last backed up

Would you like to know when any of your SQL backups were last backed up? Or did you want to verify that a back up actually ran? Why now you can!

select
 max(isnull(datediff(dd,b.backup_start_date,getdate()),0))  as 'Days since backup',
 b.backup_size,
 d.name as database_name
from  
 master..sysdatabases d with (nolock)
left join 
 msdb..backupset b with (nolock)
  on d.name = b.database_name 
   and b.backup_start_date = (select max(backup_start_date) 
    from 
     msdb..backupset b2 
    where
     b.database_name = b2.database_name
     and b2.type = 'D')
where 
 d.name != 'tempdb'
group by
 d.name, b.type, b.backup_size

This produces the following:-

Find out how big each table is for a SQLdatabase

Ever wanted to know in one swoop how big your tables are for a databse in SQL Server?

Create Table #temp (
  table_name sysname ,
  row_count int,
  reserved_size varchar(50),
  data_size varchar(50),
  index_size varchar(50),
  unused_size varchar(50)
)

SET NOCOUNT ON
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''

select
  a.table_name,
  a.row_count,
  count(*) as col_count,
  a.data_size
from
  #temp a
inner join information_schema.columns b
  on a.table_name = b.table_name
group by
  a.table_name, a.row_count, a.data_size
Order by 
  CAST(Replace(a.data_size, ' KB', '') as integer) desc

drop table #temp

This produces the following:-

Find out how big your databases are in SQL Server

Get a list of all database sizes in KB for the whole SQL server.

Create Table #temp (
    table_name varchar(200) ,
    database_size int,
    remarks varchar(50)
  )

  SET NOCOUNT ON
  insert #temp exec sp_databases

  select 
    table_name, 
    cast(database_size / 1024 as nvarchar)+ ' KB' 
  from 
    #temp
  order by 
    database_size desc

  drop table #temp

An example of what gets shown can bee seen below:-

SQL Nested Sets

1. Create a database called TreeTest and open QA and execute Setup SQL
2. Now you are ready to go. Simply select the test you with to run, then
   copy the code below, paste it into Query Analyser and hit F5!

The tree is structured in this way

• TEST 1 - _NestedSet_ConvertToAdjacency
• TEST 2 - _NestedSet_DeleteAndClose
• TEST 3 - _NestedSet_DeleteNodeAndLeafs
• TEST 4 - _NestedSet_GetAll
• TEST 5 - _NestedSet_GetDepthFullTree
• TEST 6 - _NestedSet_GetDepthSubTree
• TEST 7 - _NestedSet_GetLeafs
• TEST 8 - _NestedSet_GetNextLevel
• TEST 9 - _NestedSet_GetSinglePath
• TEST 10 - _NestedSet_GetTree
• TEST 11 - _NestedSet_GetTree
• TEST 12 - _NestedSet_InsertOnSameLevel
• TEST 13 - _NestedSet_Move
• TEST 14 - _NestedSet_MoveUpDown

Top

Test 1 - _NestedSet_ConvertToAdjacency

Converts a nested set to a Adjacency model

exec __Reset
exec _NestedSet_ConvertToAdjacency

Top
Test 2 - _NestedSet_DeleteAndClose

Deletes a node then moves any sub nodes up to close the gap

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_DeleteAndClose 7
exec _NestedSet_TestLayout

Top
Test 3 - _NestedSet_DeleteNodeAndLeafs

Deletes a node and all sub nodes then closes any gaps

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_DeleteNodeAndLeafs 2
exec _NestedSet_TestLayout

Top
Test 4 - _NestedSet_GetAll

Simply gets all the nodes

exec __Reset
exec _NestedSet_GetAll

Top
Test 5 - _NestedSet_GetDepthFullTree

Simply gets all the nodes with depths

exec __Reset
exec _NestedSet_GetDepthFullTree

Top
Test 6 - _NestedSet_GetDepthSubTree

Gets the depths of the selected node and sub nodes

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_GetDepthSubTree 2

Top
Test 7 - _NestedSet_GetLeafs

Gets all the leafs - that is nodes without sub nodes

exec __Reset
exec _NestedSet_GetLeafs

Top
Test 8 - _NestedSet_GetNextLevel

Gets all nodes that are directly below the selected node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_GetNextLevel 1

Top
Test 9 - _NestedSet_GetSinglePath

Gets a full path for a single node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_GetSinglePath 8

Top
Test 10 - _NestedSet_GetTree

Gets nodes including and below the selected node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_GetTree 2

Top
Test 11 - _NestedSet_InsertDirectlyBelow

Inserts a node directly below the selected node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_InsertDirectlyBelow 1,'new name'
exec _NestedSet_TestLayout

Top
Test 12 - _NestedSet_InsertOnSameLevel

Inserts a node on the same level (at the bottom) as the selected node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_InsertOnSameLevel 1,'new name'
exec _NestedSet_TestLayout

Top
Test 13 - _NestedSet_Move

Moves a node to below another node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_Move 6,3
exec _NestedSet_TestLayout

Top
Test 14 - _NestedSet_MoveUpDown

Moves a node on the same level either up or down (1 for up, 0 for down) to another node

exec __Reset
exec _NestedSet_TestLayout
exec _NestedSet_MoveUpDown 2,0
exec _NestedSet_TestLayout

References

• Mike Hillyer Managing Hierarchical Data in MySQL (I have used his sample data)
• Joe Celko's Trees and Hierarchies in SQL for Smarties - 2004
• 
  Alejandro Izaguirre Google groups
  

I would also like to thank:-

• Ryan O'Neill
• Nick Crowther