Tuesday 22 September 2015

Query all SQL DB sizes

Original source here

with fs
as
(
    select database_id, type, size * 8.0 / 1000 size
    from sys.master_files
)
select 
    name,
    (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


C.name

From


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

Where


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)





Select


@Maxcolumns = Max(id)


From



#Columns

--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

Begin


Insert #TablesToCompare


(SchemaName,TableName)


select distinct s.name

,t.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

Where


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

begin


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






use DATABASE1

exec (@SQL)

use DATABASE2

exec (@SQL)

set @CurrentTable=@CurrentTable+1

end





Set @CurrentColumn = @CurrentColumn + 1

set @CurrentTable=1

set @MaxTables=null


set @SQL = null


truncate table #TablesToCompare

End






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

Includes

  • 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

Contains

  • Temporary Tables
  • Calculated Columns
  • variables
  • While Loops