Finding strings in SQL is usually done within a WHERE clause – you’re searching for rows in a table containing your condition which can of course include a string. Sometimes there may be a need to search for a string across all tables within a database though, which is where this post might help out.

The TSQL script below is database-scoped and searches all tables for a string that’s inserted into the ‘valueToFind‘ variable. There’s also a variable in the script to search for a string within column names if there’s ever a random need for that too. Hope this helps!

SQL Script to Find a String in Tables

! This is by no means the best way to do this, but it should work well enough for that one-off task. !

I’ve had the script below saved for years. I don’t know who wrote it and tried to find out via Google. There are different variations of this out there but this seems to work well enough for me on SQL Server2019.

scope to the desired database before running this

I’m searching for my name with wildcards at both ends. The result is as expected within my test database and contains 2 occurrences of my name.

The screenshot above shows a row per column, for each table that has my name in it.

Now I’m going to check the data within those tables for the string I searched for.

That’s me done for this random info post. For more SQL Server Tips from a SQL DBA in the field, have a look at my SQL Server blog posts page.

When performing data analysis of email data in SQL, there’s often a need to show only the domain of an email address. You can use this for things like checking invalid emails or to find out your most populous domains.

In this blog post, I’ll show how to get this information with some queries.

The following script will create a database & table with test data in the table that we can use for a quick demo:

USE master
GO

DROP DATABASE IF EXISTS fatanta; 

CREATE DATABASE fatanta;

USE fatanta;
GO

DROP TABLE IF EXISTS players;
CREATE TABLE players (
	id INT IDENTITY PRIMARY KEY NOT NULL, 
	first_name VARCHAR(100),
	surname VARCHAR(100),
	user_role VARCHAR(40),
	email_address VARCHAR(200)
)

INSERT INTO players VALUES ('peter','whyte','master','pete_test_mail@hotmail.com')
INSERT INTO players VALUES ('john','mcdonald','admin','johnman64@gmail.com')
INSERT INTO players VALUES ('alex','thompson','member','stickman22@hotmail.co.uk')
INSERT INTO players VALUES ('fiona','thompson','member','stickmanswife@hotmail.co.uk')
INSERT INTO players VALUES ('mo','molsen','member','mo3@outlook.com')
INSERT INTO players VALUES ('desire','nicholson','member','nicholsons.desire@outlook.com')
INSERT INTO players VALUES ('don','donaldson','member','thedon@outlook.com')

Once that’s been run we can straight into selecting the data and aggregate email addresses for analysis.

This query will show the distinct email domains within a table:

-- Show distinct email domains. 
SELECT DISTINCT
	RIGHT(email_address, LEN(email_address) - CHARINDEX('@', email_address)) AS [email_domain]
FROM players
ORDER BY 1;

And this query will do the same but includes a count on email domains that are the same:

-- Show distinct email domains with counts.
SELECT 
	RIGHT(email_address, LEN(email_address) - CHARINDEX('@', email_address)) AS [email_domain],
	COUNT(*) AS [count]
FROM players
GROUP BY RIGHT(email_address, LEN(email_address) - CHARINDEX('@', email_address))
ORDER BY [count] DESC;

That’s it on this – hope it’s useful for you, my dear random visitor.