SQLsnippet

sql.json

{
	"Tips":{
		"prefix": "b'sqltips",
		"body": [
	    "--Sweaty(SELECT) Feet(FROM) Will(WHERE) Give(GROUP BY) Horrible(HAVING) Oder(ORDER BY)",
			"--SELECT",
			"--FROM",
			"--WHERE",
			"--GROUP BY",
			"--HAVING",
			"--ORDER BY",
			"--",
			"----Limitations of CTEs",
			"--1. CTEs can only be used in the current query scope, meaning they cannot be referenced after the final SELECT",
			"--2. This can be a problem if you need to reuse your virtual tables multi times for diff purposes",
			"--3. cannot be referenced individually, making degugging more difficult",
			"--4. certain optimization techniques are not available to CTEs",
			"--SQL Join Types PDF",
			"--https://drive.google.com/file/d/18i-g734BC3t08U9KaUafERi_5lNGBxp5/view?usp=sharing",
			"--hostname DESKTOP-HKIAEAM\\SQLEXPRESS"
		],
		"description": "Puts tips in comments"
	},
	"Find datatype in column":{
		"prefix": "b'find_datatype",
		"body": [
			"SELECT table_schema, table_name, collumn_name, data_type, collation_name",
			"FROM INFORMATION_SCHEMA.COLUMNS",
			"WHERE 1=WHERE 1=1",
			"AND TABLE_SCHEMA = [${1:schema-name}] AND TABLE_NAME = [${2:table-name}];",
			"--If the collation_name has CI in it then it is case insensitive"
		],
		"description": "inserts method to find data type of column"
	},
	"Find Column By Name":{
		"prefix": "b'find_column",
		"body": [
    "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE 1=1 AND COLUMN_NAME LIKE '%${1:column_name}%' ORDER BY TABLE_NAME",
		],
		"description": "asks for column name and will find it in any db table"
	},
	"Find Table by Name":{
		"prefix": "b'find_table",
		"body": [
			"SELECT [Table Names] = [name] FROM SYS.TABLES"
		],
		"description": "asks for table name to search for word in table name."
	},
	"Setup SQL Query": {
		"prefix": "b'Setup_sql",
		"body": [
			"--/*",
			"SELECT",
			" [${6:alias}] = $5.[${7:column}]",
			"FROM",
			" [${3:schema}].[${4:table}] AS ${5:table_alias}",
			"WHERE 1=1",
			";",
			"--*/",
			"\n\n",
			"$BLOCK_COMMENT_START",
			"\tFilename: ...: $TM_FILENAME",
			"\tDate ........: $CURRENT_DATE-$CURRENT_MONTH_NAME_SHORT-$CURRENT_YEAR",
			"\tTime ........: $CURRENT_HOUR:$CURRENT_MINUTE",
			"\tDesc ........: ${1:description}",
			"\tNotes........:",
			"\t\t1. ",
			"$BLOCK_COMMENT_END",
			"\n\n",
			"GO"
		],
		"description": "Creates a SQL query with a comment block"
	},
	"Add new Select statement":{
		"prefix": "b'select",
		"body": [
			"--/* ${6:ref-number} ",
			"SELECT",
			" [${4:alias}] = $3.[${5:column}]",
			"FROM",
			" [${1:schema}].[${2:table}] AS ${3:table_alias}",
			"WHERE 1=1",
			";",
			"--*/",
			"\n"
		],
		"description": "Adds Select Statement without comments or source"
	},
	"Select Count One-Line":{
		"prefix": "b'count",
		"body": [
			"SELECT [$2] = COUNT(*) FROM [${1:schema}].[${2:table}]",
		],
		"description": "Insert a one-line SELECT"
	},
	"Add Join":{
		"prefix": "b'join",
		"body": [
			"${1|INNER ,LEFT OUTER ,FULL OUTER ,RIGHT OUTER |} JOIN",
			" [${2:schema}].[${3:table}] AS ${4:table_alias}",
			"ON",
			" ${5:alias1}.[${6:table1}] = $4.[$6]"
		],
		"description": "Inserts a new join"
	},
	"Create a correlated subquery":{
		"prefix": "b'correlated-sub",
		"body": [
			", [${1:derived-column}] = ",
			"(",
			"SELECT ${7:COUNT(*)}",
			"FROM [${2:schema}].[${3:table}] AS ${4:alias}",
			"WHERE $4.[${5:column}] = ${6:alias}.[$5]",
			")"
		],
		"description": "Inserts a correlated subquery"
	},
	"Cut current query and insert as subquery":{
		"prefix": "b'subquery",
		"body": [
			"SELECT",
			" *",
			"FROM (",
			"$CLIPBOARD",
			") AS ${1:alias}",
			"WHERE [${2:filter}] = ${3:value}",
			"\n"
		]
	},
	"Pivot":{
		"prefix": "b'pivot_single",
		"body": [
			"\n",
			"--/*",
			"SELECT [${9}]",
			"\t,[${10}]",
			"\tFROM (SELECT [${4:source tbl column 1}]",
			"\t\t\t, [${5:source tbl column 2}]",
			"\t\tFROM [${1:source schema}].[${2:source tbl}]) AS ${3:alias}",
			"\t\tPIVOT(${6|SUM,AVG,MAX,MIN|}([${7:aggregate column}])",
			"\t\tFOR [${8:column contains headers}]",
			"\t\tIN([${9:pivot column 1}], [${10:pivot column 2}])) AS ${11:alias}",
			"ORDER BY 1",
			"--*/",
			"\n"
		]
	},
	"Case Statement": {
		"prefix": "b'case_setup",
		"body": [
			"[${1:first_case}] =",
			"CASE",
			"\tWHEN ${2:first_when}",
			"\t\tTHEN ${3:then_this}",
			"\tELSE ${4:else_this}",
			"END"
		],
		"description": "Inserts Case-When-Else Statement"
	},
	"When Statement": {
		"prefix": "b_when",
		"body": [
			"WHEN ${1:when_statement}",
			"\tTHEN ${2:then_statement}"
		],
		"description": "Inserts when-then statement"
	},
	"Insert CTE":{
		"prefix": "b'cte_sql",
		"body": [
			"--/*",
			"WITH Step1 AS",
			"(",
			"/* This contains inner query & derived columns */",
			"SELECT [column]",
			"FROM [${1:schema}].[${2:table}]",
			")",
			",",
			"Step2 AS",
			"(",
			"/* This contains outer querry from inner */",
			"SELECT [column]",
			"FROM Step1",
			")",
			"SELECT",
			"[column-alias]",
			",[column-alias]",
			"FROM Step2 AS A",
			"JOIN Step2 AS B",
			"ON A.matching_column = B.matching_column",
			"ORDER BY 1",
			"--*/"
		],
		"description": "Inserts a Common Table Expression"
	},
	"Insert Date Series":{
		"prefix": "b'date_series",
		"body": [
		"WITH",
    "[DateSeries] AS (SELECT CAST('${1:starting date mm-dd-yyyy}' AS DATE) AS [${3:column_name}]",
    "UNION ALL",
    "SELECT DATEADD(DAY, 1, [$3])",
    "FROM [DateSeries]",
    "WHERE 1=1",
    "AND [$3] < CAST('${2:end date mm-dd-yyyy}' AS DATE))",
    "SELECT [$3]",
    "FROM [DateSeries]",
    "OPTION(MAXRECURSION 365)"
		],
		"description": "Inserts dates in a series"
	},
	"Insert Number Series":{
		"prefix": "b'number_series",
		"body": [
    "WITH",
    "[NumberSeries] AS (SELECT 1 AS [MyNumber]",
    "UNION ALL",
    "SELECT [MyNumber] + 1",
    "FROM [NumberSeries]",
    "WHERE 1=1",
    "AND [MyNumber] < ${1:max_number})",
    "SELECT [MyNumber]",
    "FROM [NumberSeries]",
    "OPTION(MAXRECURSION $1)"
		],
		"description": "Inserts numbers in a series"
	}
}