richardbasile

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

richardbasile

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

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

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

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

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

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

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

;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

select t.name      AS table_name
     , i.name      AS index_name
     , i.type_desc AS index_type
     , i.is_unique
     , i.is_primary_key
     , s.user_seeks
     , s.user_scans
     , s.user_lookups
     , s.user_updates
     , (SELECT MAX(ts) FROM (