jeremykdev’s gists

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 / 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 / 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 / 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 / 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 / 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 / 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 / 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 / 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 '%[email protected]%'
	ORDER BY C.Path;

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;

Jeremy Knue jeremykdev