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.

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.

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'

TSQL Stored Procedure to disable failing jobs

Procedure to disable failing jobs and email an alert when this happens

SQL Pivot and Stuff

Query to pivot and stuff information to show all values 

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