stevewithington

# Obtained with the code written in next file
emoji_grinning_face=😀
emoji_grinning_face_with_big_eyes=😃
emoji_grinning_face_with_smiling_eyes=😄
emoji_beaming_face_with_smiling_eyes=😁
emoji_grinning_squinting_face=😆
emoji_grinning_face_with_sweat=😅
emoji_rolling_on_the_floor_laughing=🤣
emoji_face_with_tears_of_joy=😂
emoji_slightly_smiling_face=🙂

stevewithington

How to ssh to a remote server without typing your password

Save yourself a few keystrokes. Follow the steps below:

1. Run this Bash script on your laptop:

   #!/usr/bin/env bash
   

The hostname of your remote server.

stevewithington

USE [someDb];
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS [dbo].[usp_CreateTempTableFromTable];

stevewithington

DECLARE @filter_strings TABLE (
	filter_string NVARCHAR(255) NOT NULL
);
INSERT INTO @filter_strings(filter_string)
VALUES ('test'), ('city'), ('state'), ('title'), ('street'), ('1'), ('-'), ('.'), ('na'), ('n/a'), ('x'), ('?'), ('unknown'), ('asdf'), ('abcd'), ('abc'), ('abd') 
;

UPDATE [dbo].[someTable]
SET 
	[postal_code] = 

stevewithington

-- Update with RegEx
UPDATE t
SET 
	[someField] = 
		CASE 
			WHEN LEFT(t.[postal_code], 3) LIKE '%[a-z][0-9][a-z]%'
			THEN 'Canadian Value'
			WHEN LEFT(t.[postal_code], 3) LIKE '%[0-9][0-9][0-9]%'
			THEN 'US Value'
			ELSE NULL

stevewithington

USE [someDb];
GO;

DECLARE @table Table (id INT IDENTITY(1,1), item NVARCHAR(255) NOT NULL)
;

INSERT INTO @table (item)
  SELECT someColumn
  FROM someConfigurationTable

stevewithington

-- SQL Server Row Count for all Database Tables

USE [SomeTable]
GO

DECLARE @QueryString NVARCHAR(MAX);

SELECT @QueryString = 
	COALESCE(@QueryString + ' UNION ALL ','')
	+ 'SELECT '

stevewithington

-- Kill all transactions in a database during a restore to avoid issues with exclusive access.

DECLARE @SQL VARCHAR(8000) 

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '  
FROM sys.sysprocesses  
WHERE DBID=DB_ID('AdventureWorks') 

PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute 

stevewithington

Bash ANSI Variables for Colorized Echo Output

I wanted to be able to make some messaging stand out while running Azure Pipeline Builds. This is ultimately what worked for me.

Example Usage

Some simple examples of how to use the variables listed below.

One Color for the Entire Line

stevewithington

/* SQL: Get total and average row count per table and/or database */

USE SomeDB;
GO

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)