Skip to content

Instantly share code, notes, and snippets.

@andyj
andyj / find-MSSQL-table-on-server.sql
Created October 26, 2015 22:29
Search all MSSQL databases on a server for a table
sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%tableNameYouAreSearchingFor%'''
@andyj
andyj / findSPByName.sql
Created October 26, 2015 22:51
Searching MSSQL database for a Stored Proc by name
sp_MSforeachdb 'SELECT "?" AS DB,
r.SPECIFIC_SCHEMA, r.routine_name, r.ROUTINE_DEFINITION, p.ORDINAL_POSITION, p.PARAMETER_MODE, p.PARAMETER_NAME, p.DATA_TYPE
FROM [?].INFORMATION_SCHEMA.ROUTINES r
JOIN [?].INFORMATION_SCHEMA.PARAMETERS p ON r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE
r.ROUTINE_TYPE = ''PROCEDURE''
AND left(r.ROUTINE_NAME,2) <> ''dt''
AND r.routine_name LIKE ''%yourStoredProcName%''
ORDER BY r.SPECIFIC_NAME ASC, p.ORDINAL_POSITION ASC'
@andyj
andyj / findSPByDefinition.sql
Created October 26, 2015 22:53
Searching MSSQL database for a Stored Proc by T-SQL Definition
sp_MSforeachdb 'SELECT "?" AS DB,
r.SPECIFIC_SCHEMA, r.routine_name, r.ROUTINE_DEFINITION, p.ORDINAL_POSITION, p.PARAMETER_MODE, p.PARAMETER_NAME, p.DATA_TYPE
FROM [?].INFORMATION_SCHEMA.ROUTINES r
JOIN [?].INFORMATION_SCHEMA.PARAMETERS p ON r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE
r.ROUTINE_TYPE = ''PROCEDURE''
AND left(r.ROUTINE_NAME,2) <> ''dt''
AND r.ROUTINE_DEFINITION LIKE ''%someSQLCode%''
ORDER BY r.SPECIFIC_NAME ASC, p.ORDINAL_POSITION ASC'
@andyj
andyj / gist:11eb25b52d4d72df3f80
Last active March 17, 2016 12:45
IP4 IPTable rules
# Found on https://nodeswat.com/blog/setting-up-a-secure-node-js-web-application/
# Slightly modified
*filter
# Default policy is to drop all traffic
-P INPUT DROP
-P FORWARD DROP
-P OUTPUT DROP
@andyj
andyj / gist:4db2d83e4cccf811099e
Created March 17, 2016 12:47
IP6 IPTables rules
#Block all IP6 traffic
*filter
# Reject all IPv6 on all chains.
-A INPUT -j DROP
-A FORWARD -j DROP
-A OUTPUT -j DROP
COMMIT
@andyj
andyj / Change_all_CHARACTER_SET_in_a_Mysql_database.sql
Last active April 8, 2016 10:15
Change all CHARACTER SETs in a Mysql database.sql
-- This pull back a list of tables which have NON-UTF8 encoded columns and creates a SQL script to fix them (and the table itself)
SELECT CONCAT("ALTER TABLE ",t.TABLE_SCHEMA,".",t.TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci;",
"ALTER TABLE ",t.TABLE_SCHEMA,".",t.TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;")
AS sql_to_copy_and_run
FROM information_schema.TABLES t
INNER JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE
t.TABLE_SCHEMA = ? -- Replace ? with your Database/Schema name
AND t.table_type = 'BASE TABLE' AND character_set_name IS NOT NULL AND character_set_name <> 'utf8'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
@andyj
andyj / nginx.conf
Last active November 16, 2020 01:36 — forked from tonyjunkes/nginx.conf
Server portion for setting up a proxy to Lucee with rewrite to have index.cfm omitted for SES.
http {
...
server {
listen 80;
server_name example.com;
root /var/www/exmaple.com/www/;
index index.cfm;
set $lucee_context "example.com";
@andyj
andyj / bash_script_to_back_up_mysql_to_s3.sh
Last active April 2, 2019 08:54
Bash script to backup a MySQL RDS dump to S3
# From Andy Jarretts post:
# http://www.andyjarrett.co.uk/2016/08/02/secured-bash-script-to-backup-a-mysql-rds-dump-to-s3/
#!/bin/bash
SQLDUMP="$(date +'%Y%m%d%H%M').sql.gz"
echo "Creating backup of database to $SQLDUMP"
mysqldump --login-path=local --databases YourDatabaseName | gzip -9 > $SQLDUMP
echo "Dump Zipped up"
@andyj
andyj / Sv4Util.cfc
Created March 6, 2017 12:04 — forked from Leigh-/Sv4Util.cfc
Amazon Web Services Signature 4 Utility for ColdFusion (Alpha)
/**
* Amazon Web Services Signature 4 Utility for ColdFusion
* Version Date: 2016-04-12 (Alpha)
*
* Copyright 2016 Leigh (cfsearching)
*
* Requirements: Adobe ColdFusion 10+
* AWS Signature 4 specifications: http://docs.aws.amazon.com/general/latest/gr/signature-version-4.html
*
* Licensed under the Apache License, Version 2.0 (the "License");
@andyj
andyj / docker_commands.sh
Last active July 22, 2019 14:15
Essential Docker commands for .bash_profiles wrapped up as terminal functions
MY_IP=127.0.0.1
#Ubuntu
#MY_IP=$(wget -qO- icanhazip.com)
#MAC
#MY_IP=$(ifconfig en0 | grep "inet 1" | awk '/inet/ { print $2 } ')
echo Current IP: $MY_IP