Richard Basile richardbasile

Cloud Architect and Database Engineer

richardbasile / uncommittedTxns.sql

Created May 24, 2019 12:40

Find uncommitted transactions in PostgreSQL

	select *
	from pg_stat_activity
	where (state = 'idle in transaction')
	and xact_start is not null ;

richardbasile / blockedQueries.sql

Created May 24, 2019 12:38

Finding blocked queries and their blockers in PostgreSQL

	with blocked as (
	SELECT pid AS blocked_pid
	, usename AS blocked_username
	, query AS blocked_query
	, query_start AS blocked_start
	, wait_event AS blocked_wait_event
	, wait_event_type AS blocked_event_type
	, unnest( pg_blocking_pids( pid ) ) AS blocked_by
	FROM pg_stat_activity
	WHERE cardinality( pg_blocking_pids( pid ) ) > 0

richardbasile / meteors.py

Created December 22, 2018 18:31

Find nearest meteor fall to Whitehouse

	import requests
	import os
	import math

	key=os.environ['GOOGLE_API_KEY']
	address='1600 Pennsylvania Ave NW, Washington, DC 20500'
	endpoint='https://maps.googleapis.com/maps/api/geocode/json?'

	response = requests.get(endpoint + 'address=' + address + '&key=' + key)
	location=response.json()['results'][0]['geometry']['location']

richardbasile / logBackups.sql

Created March 16, 2018 13:13

SQL Server - Log Backups

	DECLARE @db VARCHAR(128)

	DECLARE c CURSOR FOR
	SELECT [name] FROM sys.databases
	WHERE recovery_model_desc = 'FULL'
	AND [name] NOT IN ('master','msdb','model','tempdb','aspstate')

	OPEN c
	FETCH NEXT FROM c INTO @db

richardbasile / dailyBackups.sql

Created March 16, 2018 13:12

SQL Server - Daily Backups

	BEGIN

	SELECT [name], [recovery_model_desc], sys.fn_hadr_is_primary_replica([name]) as [pr] FROM sys.databases

	DECLARE @db VARCHAR(128)

	DECLARE @targets TABLE (db varchar(128))
	INSERT INTO @targets (db) VALUES ('MyDB'), ('YourDB')
	DECLARE c CURSOR FOR
	SELECT [db] FROM @targets

richardbasile / rebuildIndexes.sql

Created March 16, 2018 13:10

SQL Server - Rebuild Indexes

	BEGIN
	set quoted_identifier on

	DECLARE @db varchar(max) = 'MyDB'
	DECLARE @today VARCHAR(9) = datename(w,sysdatetime())

	DECLARE @tableName varchar(max)
	DECLARE @indexName varchar(max)
	DECLARE @sql Nvarchar(max)

richardbasile / renameDefaultConstraints.sql

Created March 16, 2018 13:04

SQL Server - Rename Default Constraints

	SELECT 'exec sp_rename N''dbo.[' + d.name + ']'', ''DF_' + upper(t.name) + '_' + lower(c.name) + ''';' cmd
	from sys.tables t
	join sys.columns AS c on c.object_id = t.object_id
	JOIN sys.default_constraints AS d on d.parent_object_id = t.object_id and d.parent_column_id = c.column_id
	where t.schema_id = 1
	and d.name <> 'DF_' + upper(t.name) + '_' + lower(c.name)
	order by 1

richardbasile / renameForeignKeys.sql

Created March 16, 2018 13:03

SQL Server - Rename Foreign Keys

	select 'exec sp_rename N''dbo.[' + fk.name + ']'', N''FK_' + upper(t.name) + '_' + lower(c.name) + ''', @objtype = ''OBJECT'';'
	from sys.foreign_keys fk
	join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
	join sys.columns c on c.object_id = fk.parent_object_id
	and c.column_id = fkc.parent_column_id
	join sys.tables t on t.object_id = fk.parent_object_id
	where fk.name <> 'FK_' + upper(t.name) + '_' + lower(c.name)

richardbasile / renameIndexes.sql

Created March 16, 2018 13:03

SQL Server - Rename Indexes

	;WITH details AS(
	select i.object_id
	, upper(t.name) as table_name
	, i.index_id
	, i.name as index_name
	, i.is_unique_constraint
	, i.is_primary_key
	, CASE is_primary_key WHEN 1 THEN 'PK_'
	ELSE
	CASE is_unique_constraint

richardbasile / indexUsage.sql

Last active March 16, 2018 16:21

SQL Server - Index Usage