Skip to content

Instantly share code, notes, and snippets.

View jeremykdev's full-sized avatar

Jeremy Knue jeremykdev

View GitHub Profile
@jeremykdev
jeremykdev / email-query-results-as-csv.py
Created May 4, 2021 12:36
Prototype python script generate and email a report: query a database, generate a CSV file, and email the CSV file
# Prototype generate a CSV report file and send an eml file using python
# Prerequisites
#
# Pyodbc
# https://github.com/mkleehammer/pyodbc
# pip install pyodbc
#
# This example uses Microsoft SQL Server's pubs sample database
# https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs
@jeremykdev
jeremykdev / create-eml-file.py
Created May 1, 2021 21:04
Python script to create an eml file
# Prototype to create an eml file using python
import os
import uuid
from email import generator
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
# where to write the output file
directory = "C:\\Users\\Jeremy\Documents\\python\\email-prototype\\temp"
@jeremykdev
jeremykdev / odbc-query-to-csv.py
Created May 1, 2021 20:36
Python Script to Query Database and Create a CSV File From the Query Results. Uses pyodbc
# Prototype python script to query database using pyodbc
# Prerequisites
#
# Pyodbc
# https://github.com/mkleehammer/pyodbc
# pip install pyodbc
#
# This example uses Microsoft SQL Server's pubs sample database
# https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs
@jeremykdev
jeremykdev / find-objects-schemas-with-reserved-word-names.sql
Created July 30, 2016 18:12
Find objects and schemas in a Microsoft SQL Server database whose names are reserved words
-- use table variable to store list of reserved words
DECLARE @reserved TABLE (
Word VARCHAR(255) NOT NULL
);
INSERT INTO @reserved ( Word )
VALUES
('ADD'),
('EXISTS'),
('PRECISION'),
@jeremykdev
jeremykdev / find-tables-columns-using-depricated-data-types.sql
Last active July 30, 2016 18:13
Find Microsoft SQL Server database tables which include columns using depricated data types
-- see: https://msdn.microsoft.com/en-us/library/ms143729.aspx
SELECT
TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
@jeremykdev
jeremykdev / files-by-size.sql
Created July 16, 2016 17:22
Find Microsoft SQL Server files by size
USE master;
SELECT
DB.name AS DatabaseName
, F.type_desc AS FileType
, F.size AS [SizeIn8KbPages]
, F.physical_name AS [PysicalFileName]
FROM sys.databases AS DB
INNER JOIN sys.master_files AS F ON ( DB.database_id = F.database_id )
@jeremykdev
jeremykdev / tables-without-an-identity-column.sql
Created July 16, 2016 17:14
Find Microsoft SQL Server database tables without an identity column
SELECT
S.name AS SchemaName
,T.name AS TableName
FROM sys.tables AS T
INNER JOIN sys.schemas AS S ON ( T.schema_id = S.schema_id )
WHERE NOT EXISTS (
SELECT *
FROM sys.columns AS C
@jeremykdev
jeremykdev / tables-without-a-primary-key.sql
Created July 16, 2016 17:05
Find tables in a Microsoft SQL Server database without a primary key defined
SELECT
S.name AS SchemaName
,T.name AS TableName
FROM sys.tables AS T
INNER JOIN sys.schemas AS S ON ( T.schema_id = S.schema_id )
WHERE NOT EXISTS (
SELECT *
FROM sys.indexes AS I
@jeremykdev
jeremykdev / ReportSubscriptionByEmailAddress.sql
Created April 22, 2014 21:28
Query to find reports which are scheduled to be send to a given email address using a subscription in Microsoft SQL Server Reporting Services..
USE ReportServer;
SELECT C.Path
FROM dbo.Subscriptions AS S
INNER JOIN dbo.Catalog AS C
ON ( S.Report_OID = C.ItemID )
WHERE ExtensionSettings LIKE '%target@example.com%'
ORDER BY C.Path;
@jeremykdev
jeremykdev / generate-statements-drop-all-foreign-keys.sql
Created March 31, 2014 16:33
Generate SQL statements to drop all foreign keys in a Microsoft SQL Server database
SELECT 'ALTER TABLE ' + QUOTENAME(S.name) + '.' + QUOTENAME(T.name) + ' DROP CONSTRAINT ' + QUOTENAME(FK.name) + ';'
FROM sys.foreign_keys AS FK
INNER JOIN sys.tables AS T
ON ( Fk.parent_object_id = T.object_id )
INNER JOIN sys.schemas AS S
ON ( T.schema_id = S.schema_id )
ORDER BY S.name, T.name, FK.name;