Tuesday, 22 September 2015

Query all SQL DB sizes

Original source here

with fs
    select database_id, type, size * 8.0 / 1000 size
    from sys.master_files
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

However not all users have these permissions, script below to check a single table across multiple dbs.

Wednesday, 29 July 2015

Stored Procedure that disables failing Server Agent Jobs

I had a problem, I had a lot of development jobs that ran overnight, when they failed I got an email advising me of this. Then they failed again and again as they kept trying to run, this stored procedure monitors for failing jobs, using a user set limit and emails out a VERY bright red email to advise on what it's done.

Thursday, 4 June 2015

Script to iterate through all selected columns in two databases

Script that does a distinct count and comparison of all columns and all tables across two databases
--list of fields to check, using a table in SQL listing all tables to be checked

Create --drop

Table #Columns


id Int Identity(1 , 1)

, ColumnName Varchar(500)


Insert #Columns

( ColumnName


Select Distinct



sys.columns C

Inner Join sys.tables t

On t.object_id = C.object_id

Inner Join sys.schemas s

On s.schema_id = t.schema_id

Inner Join Process.TableOutcomes TOut

On TOut.TableName = t.name


s.name = 'Reporting'

And TOut.isActive = 1

And C.name <> 'refreshdate'

And C.name Not Like '%&%'

Declare @Maxcolumns Int , @CurrentColumn Int = 1 , @MaxTables int , @CurrentTable Int = 1 , @SQL varchar(max) , @ColumnName varchar(255)


@Maxcolumns = Max(id)



--create tables, as limited to 1200 columns per table, split into two

Create Table #AllTables3


AllTableID Int Identity(1 , 1)

, DBName Varchar(100)

, SchemaName Varchar(255)

, TableName Varchar(255)

, ColumnName Varchar(255)

, TotalRowCount BigInt


Create Table #TablesToCompare


ID BigInt Identity(1 , 1)

, SchemaName Varchar(255)

, TableName Varchar(255))

--Add first 1000 columns to first table

While @CurrentColumn <= @Maxcolumns


Insert #TablesToCompare


select distinct s.name


from sys.tables t

left join sys.columns C on t.object_id=c.object_id

left join #Columns co on co.ColumnName=c.name

left join sys.schemas s on t.schema_id = s.schema_id

left join Process.TableOutcomes Tot on Tot.TableName=t.name


Tot.isActive=1 and co.id=@CurrentColumn;

select @ColumnName = c.ColumnName

from #Columns C

where c.id = @CurrentColumn

select @MaxTables=MAX(ID) from #TablesToCompare

while @CurrentTable<=@MaxTables


select @SQL = 'insert #AllTables3 (DBName,SchemaName,TableName,ColumnName,TotalRowCount)

select [DBName] = +DB_NAME()

,[SchemaName] = '''+T.SchemaName+'''

,[TableName] = '''+T.TableName+'''

,[ColumnName] = '''+@ColumnName+'''

,count(distinct ['+@ColumnName+']) from '+T.SchemaName+'.'+T.TableName

from #TablesToCompare T

where t.ID = @CurrentTable


exec (@SQL)


exec (@SQL)

set @CurrentTable=@CurrentTable+1


Set @CurrentColumn = @CurrentColumn + 1

set @CurrentTable=1

set @MaxTables=null

set @SQL = null

truncate table #TablesToCompare


select * from #AllTables3

where TableName<>'ms_InterestSplit'

Thursday, 22 January 2015

Thursday, 8 January 2015

SQL Claims Triangles

Insurance claims triangles, getting the latest outstanding amount and the total amount paid


  • Temporary Tables
  • Updating tables using a join
  • While Statements
  • Alter statements

SQL Earned Premiums for Triangulations

Query to generate earned premiums, working based off the latest of either the entry date or the inception date


  • Temporary Tables
  • Calculated Columns
  • variables
  • While Loops