Drop all tables that start with underscore

In an ironic twist of fate I adapted my ‘Drop all temp-tables’ script, to drop all tables beginning with an underscore.

Yes, it is ironic, because it uses a temp-table to store the working list of tables to be deleted. Whilst my original script used a real table to store a list of temp-tables.

Well … ok then

-- DropAllTablesStartingWithAnUnderscore.sql

IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
SELECT [name] 
INTO #tables
FROM sys.tables
WHERE [name] LIKE '/_%' ESCAPE '/'

DECLARE @table VARCHAR(200), @cmd VARCHAR(500)
WHILE (SELECT COUNT(*) FROM #tables) > 0
	SET @table = (SELECT TOP(1) [name] FROM #tables)
	SET @cmd = 'drop table ' + @table
	DELETE FROM #tables WHERE [name] = @table

(In case you were wondering why I created these underscore-tables in the first place. I was refactoring a stored-procedure that took over an hour to run, and had a large number of temp-tables. I wanted to persist those temp-tables for another day, and not have to start from scratch.)

Find the partner of a bracket in SSMS

Faced with a barrage of tabbed T-SQL in SSMS it can sometimes be quite difficult to see the close bracket that signifies – for example – the end of a CTE.

TIP: swipe from the inside of the bracket outwards to highlight (in grey) the bracket itself, and also its partner. EG: swipe right-to-left across an open bracket.

Running CHECKDB on TempDB

Normally I would not bother, but when CHECKDB runs on TempDB it cannot use a snapshot so has to lock all the temp tables. This script will wait for exclusive access for up to a minute.

DECLARE @outcome VARCHAR(50) = 'TempDB is currently too busy for CHECHDB', 
	@endtime DATETIME = DATEADD(mi,1,GETDATE())
WHILE GETDATE() < @endtime
  IF NOT EXISTS (SELECT 1 FROM sys.dm_tran_locks WHERE request_mode = 'X' AND resource_database_id = 2)
    DBCC CheckDB([tempdb]) WITH NO_INFOMSGS;
    SET @outcome = 'success'
  WAITFOR DELAY '00:00:01';
SELECT @outcome

A TDE test restore

Post migration, I wanted to make sure an encrypted database on a new SQL 2014 Enterprise edition server could be restored.

I installed SQL 2014 Developer edition on a second machine and initially got the expected error …

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint.
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

1. I checked if the target server was enabled for TDE …

SELECT * FROM sys.symmetric_keys

This returned one row, I checked against the source server and that returned two rows. I concluded that the target server was NOT yet TDE enabled.

2. To enable TDE on the target …

USE Master
BY PASSWORD = '[SomePwIJustMadeUp]';

3. Next I copied over the two files (*.cer and *.pvk) from the backup location to the target server and installed the certificate into Master …

USE Master
CREATE CERTIFICATE [NameOfTheDatabase]_Cert2
FROM FILE = '[PathAndNameOfLocalCopyOfCertFile].cer'
WITH PRIVATE KEY (FILE = N'[PathAndNameOfLocalCopyOfPvkFile].pvk',
	  DECRYPTION BY PASSWORD ='[TheSourcePwIGotFromKeepAss]');

4. After which I was able to restore the database as normal.

Docker installs

The traditional way to the install something like 7zip on a Windows laptop would be:-

  • Use Google to find the software’s website.
  • Go there and download a zip or MSI file
  • Run the (unzipped) installer or MSI file
  • Start the software by clicking a desktop icon or finding it in “all programs”.

The equivalent steps in docker would be:-

  • Find the software on docker hub with docker search
  • Downloads the software image with docker pull
  • Create a container from the image with docker create
  • Start the software by running the container with docker start

However, a better way would be:-

  • Find the software on docker hub with docker search
  • Download, unpack, install, and start the software with docker run