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.
SQL Dumping Ground
Tuesday, 22 September 2015
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'
Friday, 20 February 2015
TSQL Stored Procedure to disable failing jobs
Procedure to disable failing jobs and email an alert when this happens
Labels:
admin,
email,
rank,
SQL,
stored procedure,
temp tables
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
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
Contains
- Temporary Tables
- Calculated Columns
- variables
- While Loops
Subscribe to:
Posts (Atom)