🔥

Mikael Weaver mikaelweave

🔥

Software Engineer in Healthcare at Microsoft

mikaelweave / drop_sql_index_if_existst.sql.sql

Created November 18, 2015 16:57

Drop Index if it already exists

	IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')
	DROP INDEX indexname ON SCHEMA.tablename;

mikaelweave / index_last_rebuilt.sql

Created November 5, 2015 16:50

This is a collection of SQL scripts that will give you some information about your indexes.

	DECLARE @tableName VARCHAR(256);
	SET @tableName = 'dbo.ct_pstor';

	SELECT name AS Stats,
	STATS_DATE(object_id, stats_id) AS LastStatsUpdate
	FROM sys.stats
	WHERE object_id = OBJECT_ID(@tableName)
	and left(name,4)!='_WA_';

mikaelweave / SQL Stats.sql

Created September 22, 2015 06:20

Two liners to enable SQL Stats for your query/session

	SET STATISTICS IO ON;
	SET STATISTICS TIME ON;

mikaelweave / add_column_if_not_exist.sql

Created July 15, 2015 22:29

Add column if it doesn't already exist

	IF EXISTS (
	SELECT *
	FROM sys.columns
	WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
	AND name = 'ColumnName'
	)
	ALTER TABLE dbo.Person
	ADD ColumnName VARCHAR(64)

mikaelweave / compare_two_date_ranges

Created April 9, 2015 18:34

SQL logic for comparing two date ranges

	FROM - http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap
	Let ConditionA Mean DateRange A Completely After DateRange B
	(True if StartA > EndB)

	Let ConditionB Mean DateRange A Completely Before DateRange B
	(True if EndA < StartB)

	Then Overlap exists if Neither A Nor B is true
	( If one range is neither completely after the other,
	nor completely before the other, then they must overlap)

mikaelweave / drop_view_if_exists.sql

Created April 7, 2015 18:06

Drop SQL View if it exists

	IF EXISTS(SELECT * FROM sys.views WHERE name = 'e_note' AND schema_id = SCHEMA_ID('dbo'))
	DROP VIEW dbo.e_note
	GO

mikaelweave / SQL find table by name.sql

Created March 19, 2015 23:03

Scan all SQL tables in a database by name

mikaelweave / blank_stored_procedure_template.sql

Last active January 22, 2016 22:25

Template for a blank MS-SQL (T-SQL) stored procedure.

	IF OBJECT_ID('MySproc', 'P') IS NOT NULL
	DROP PROC MySproc
	GO

	CREATE PROC MySproc
	AS
	BEGIN
	[CODE HERE]
	END

mikaelweave / delete_multiple_rows.sql

Last active August 29, 2015 14:10

Delete multiple rows on row at a time

	IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP

	--This will work since each delete is done as a single row
	SELECT RowNum = ROW_NUMBER() OVER(ORDER BY ct_id), ct_id
	INTO #TEMP
	FROM ct_note where ntype_id IN('{d94f685c-84ec-49b2-8e0f-ebaf9f6e609f}', '{afbf6d79-6d77-41e6-8790-9505e9b9ed82}', '{6cad622d-c3b7-4907-b62a-a882902ee4e4}')


	DECLARE @MaxRownum INT
	SET @MaxRownum = (SELECT MAX(RowNum) FROM #TEMP)

mikaelweave / convert date time.sql

Last active November 9, 2017 16:50

Convert SQL DATETIME to superior human DATETIME

	SELECT
	FORMAT(GETDATE(), 'g'),
	CONVERT(VARCHAR(32), CAST(GETDATE() AS DATE), 101) AS d,
	REPLACE(REPLACE(RIGHT(CAST(GETDATE() AS DATETIME), 7), 'AM', ' AM'), 'PM', ' PM') AS t