Tag Archives: specific word

Find and drop every table in a SQL Server Database that contains specific text in the table name

Database Administrator, GDPR, SQL Server, T-SQL, tsql, , , , , , , , , , , , , ,

GDPR compliance has given people working in the DBA space the exciting opportunity to drop tables! Tables once considered gold mines are now being treated like live grenades management want rid of fast. This is a prudent stance because if a table contains personal data and it’s not being used for some vital business process why keep it around now? If somehow the wrong person gained access to the data it could have severe reputational and financial consequences. Of course a business should have never kept unneeded personal data but in truth most companies have gathered as much data as they could up until this point even if it wasn’t used as the assumption has been it might be needed later.

Before dropping tables though it is still good practice to rename the table first for a period of time to make sure nothing breaks. Once a sufficient amount of time has passed and you are confident the tables can be dropped without adverse effects the script below can help drop the newly unwanted tables.

If you’ve followed a standard naming convention for renaming unwanted tables, for example prefixing all the targeted tables with “_DropThis_” or something to that effect, this script will provide commands to:

  • Count the number of rows in each targeted table
  • Drop each targeted table
  • Confirm each targeted table has been dropped.

Simply find & replace the text “DatabaseName” with the name of the database that contains the tables to be dropped and “TextTarget” with the text each table name should contain and run the script.

Running the script will produce two tables. The first table will contain the commands to count the number of rows for each targeted table. The second table will contain the commands to drop each targeted table. Open new windows in SSMS, referencing the database to run the commands against, and copy and paste the scripts of each table into the windows. Run the count script first obviously before the drop script. Once the drop script has been run you can run the commented out query at the end of the script below to confirm the tables have been dropped.

You can take the screen shots and/or copy and paste the results of the commands (and the commands themselves) into an email or text document as a simple report to confirm the number of rows dropped and that the tables have been dropped.

/*
Find & Replace:
DatabaseName
TextTarget
*/
USE [DatabaseName];
GO

DECLARE @keyword AS VARCHAR(MAX);
DECLARE @MaxRow AS INT;

SET @keyword = 'TextTarget'

IF OBJECT_ID('tempdb.dbo.#TableStats', 'U') IS NOT NULL
	DROP TABLE #TableStats;

IF OBJECT_ID('tempdb.dbo.#CountCommand', 'U') IS NOT NULL
	DROP TABLE #CountCommand;

/*
Confirm Table Existance
*/
SELECT s.NAME AS SchemaName
	,t.*
INTO #TableStats
FROM sys.tables AS t
LEFT JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE t.NAME LIKE '%' + @keyword + '%'
ORDER BY s.NAME ASC
	,t.NAME ASC;

/*
Create Count SQL Commands
*/
SELECT ROW_NUMBER() OVER (
		ORDER BY [--SqlCommand]
		) AS Row#
	,[--SqlCommand]
INTO #CountCommand
FROM (
	SELECT 'SELECT ''' + s.NAME + '.' + t.NAME + ''' AS SchemaTableName, COUNT (*) AS [TableRowCount] FROM ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' WITH (NOLOCK) UNION ALL' AS [--SqlCommand]
	FROM sys.tables AS t
	LEFT JOIN sys.schemas AS s ON t.schema_id = s.schema_id
	WHERE t.NAME LIKE '%zzz%'
	) AS SqlCommand
ORDER BY [--SqlCommand];

SET @MaxRow = (
		SELECT MAX(Row#)
		FROM #CountCommand
		);

UPDATE #CountCommand
SET [--SqlCommand] = LEFT([--SqlCommand], LEN([--SqlCommand]) - 9)
WHERE Row# = @MaxRow;

UPDATE #CountCommand
SET [--SqlCommand] = [--SqlCommand] + ';'
WHERE Row# = @MaxRow;

SELECT *
FROM #CountCommand
ORDER BY [--SqlCommand];

/*
Create Drop Table SQL Commands
*/
SELECT 'DROP TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ';' AS '--DropTableSqlCommand'
FROM sys.tables AS t
LEFT JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE t.NAME LIKE '%' + @keyword + '%'
ORDER BY s.NAME ASC
	,t.NAME ASC;
/*
Run seperately
Confirm Tables are dropped
*/
	/*
SELECT s.NAME AS SchemaName
	,t.NAME AS TableName
FROM sys.tables AS t
LEFT JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN #TableStats AS ts ON s.NAME = ts.SchemaName
	AND t.NAME = ts.NAME
ORDER BY s.NAME ASC
	,t.NAME ASC;
*/

 

How to disable all enabled jobs in SQL Server with T-Sql where the job name contains specific keyword text

Database Administrator, dba, Development, How To, SQL Server, System, T-SQL, tsql, , , , , , , , , , , , , , ,

Running the below script will output the T-Sql required to disable all enabled jobs on the SQL Server instance where the job name contains a specific keyword. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

For example, if you change the text value ‘PLACEHOLDER’, for the variable @KeyWord, to ‘Backup’ then any job which has ‘Backup’ in its name will have T-Sql generated to disable it. You could change the PRINT to EXEC but I wouldn’t recommend it.

DECLARE @KeyWord AS VARCHAR(256)

SET @KeyWord = 'PLACEHOLDER'
SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.NAME
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
	AND j.NAME LIKE '%' + @KeyWord + '%' 
ORDER BY j.NAME ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_job @job_name = ' + '''' + @JobName + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END