Helpful SQL Queries

Here's just a couple of queries I often use when report writing and I need to "find" the data. These queries were courtesy of much time on stack overflow a few years back. These have since received a couple of tweaks and stay in my query recipe book.

• Finding the tables/columns

DECLARE @SearchString VARCHAR(100)
SET @SearchString = 'Phrase You Want To Search'

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%'+ @SearchString +'%'
ORDER BY    TableName,ColumnName;

This will provide a list of tables (and the exact column name) in the database that matches your phrase. Notice the '%' in the where statement allows for partial matching (so "code" would return both tables with a table_code column and a part_code column).

This is helpful before running the next queries as the next queries can timeout or slow the server if you aren't specific enough!

• Finding a Table/Column that has a specific value

If you are looking for a specific string value you can use the below query, just replace the SearchString parameter with your value. You will also need to know what column names that value should be in so update the ColumnNameString appropriately. Use the first query if you aren't sure the exact name the column may have. Notice you can add partial matching to the column names by adding "%" to the value. No need for the search string as the generated query already does it.

DECLARE @SQL VARCHAR(MAX)
DECLARE @SearchString VARCHAR(100)
DECLARE @ColumnNameString VARCHAR(100)
SET @SQL=''

-- ------------------------------------------
-- Enter the search info here :
SET @SearchString='entry value to search'
SET @ColumnNameString = '%COLUMNNAME%'
-- ------------------------------------------

SELECT   @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
         + A.ColumnName + '''+'' on table '' + ''' + A.TableName +
         ''' [Matches for '''+ @SearchString +''':] FROM ' +
         QUOTENAME(A.TableName) + ' WHERE ' + QUOTENAME(A.ColumnName) +
         ' LIKE ''%' + @SearchString +
         '%'' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10)

FROM     
(SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS 
WHERE       COLUMN_NAME LIKE @ColumnNameString) A

-- Strip off the last UNION ALL
IF LEN(@SQL)>12
  SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)

EXEC(@SQL)

--Uncomment below to see what the generated SQL search is if you run into errors
--SELECT @SQL 

Here's a version for numeric value searches.

DECLARE @SQL VARCHAR(MAX)
--DECLARE @SearchNumber DECIMAL(12,0)
DECLARE @SearchNumber INT
DECLARE @ColumnNameString VARCHAR(100)
SET @SQL=''

-- ------------------------------------------
-- Enter the search info here :
SET @SearchNumber = 2737
SET @ColumnNameString = 'COLUMNNAME'
-- ------------------------------------------

SELECT   @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
         + A.ColumnName + '''+'' on table '' + ''' + A.TableName +
         ''' [Matches for '''+ CONVERT(VARCHAR(MAX),@SearchNumber) +''':] FROM ' +
         QUOTENAME(A.TableName) + ' WHERE ' + QUOTENAME(A.ColumnName) +
         ' = ' + CONVERT(VARCHAR(MAX),@SearchNumber) +
         ' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10)
FROM     
(SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS 
WHERE       COLUMN_NAME LIKE @ColumnNameString) A

-- Strip off the last UNION ALL
IF LEN(@SQL)>12
  SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)

EXEC(@SQL)
--SELECT @SQL 

These search queries can take some time/resources to run so don't go crazy by trying to search every column by putting the value "%" in the ColumnNameString.

Here's an example of what this query could accomplish:

AI Disclaimer - I used AI to generate the header image, but i didn't like it so i cropped it purposely poorly bc in an attempt to make it more interesting looking.