November 27, 2025 at 4:09 am
Hi,
I want to find all the tables name under a database where tables having text like "ID".
Like in a database for example we have Table A, Table B----> Table F' and only table C, table E have text like ID for any of the column value
Table C
Name Product Department
SS ID@s.com CS
AS DD IT
WW ID@s.com EC
Table E
Sales Purchase GName
SS 700 ID@s.com
AS 500 IT
WW 300 ID@s.com
So I want a query which will scan all database tables and return me only the above table name as it contains ID@s.com
November 28, 2025 at 8:28 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 28, 2025 at 8:42 pm
Sounds like you have serious design issues in your database. I don't envy you one bit.
But you can use a query like this to get all the column names and all the table names of user tables:
SELECT ao.object_id,
ao.type,
ao.type_desc,
ao.name AS TableName,
ac.Name AS ColumnName
FROM sys.all_objects ao
INNER JOIN sys.all_columns ac
ON ao.object_id = ac.object_id
WHERE ao.type = 'U'
then I think you'd have to create a cursor to loop over those and use dynamic SQL to search each column and then do something like get the number of records returned (just do a count), and if there are records returned, insert the table name and column name into a table that logs where the values were found.
November 30, 2025 at 3:26 am
This is what I came up with... (btw... with that many points, you can't post CREATE TABLE etc statements for people?)
--CREATE TABLE LogTable (tableName NVARCHAR(50), columnName NVARCHAR(50));
--GO
-- Declare a FF cursor
DECLARE tableCol_cursor CURSOR FAST_FORWARD
FOR
SELECT
ac.name AS ColumnName,
ao.name AS TableName
FROM sys.all_columns ac
INNER JOIN Bakery.sys.types t
ON ac.system_type_id = t.system_type_id
INNER JOIN sys.all_objects ao
ON ao.object_id = ac.object_id
WHERE ao.type_desc= 'USER_TABLE'
AND t.Name IN ('varchar','nvarchar','text','ntext')
AND ao.object_id > 0;
-- Open the cursor
OPEN tableCol_cursor;
-- Declare variables to hold the fetched data
DECLARE @columnName NVARCHAR(50);
DECLARE @tableName NVARCHAR(50);
-- Search string variable...
DECLARE @SearchString VARCHAR(10) = 'ID@s.com';
-- Fetch the first row from the cursor
FETCH NEXT FROM tableCol_cursor INTO @columnName, @tableName;
-- Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLStmt NVARCHAR(500);
-- print the table name and column name: delete later
--PRINT 'table: ' + @tableName + ', ' + 'column: ' + @columnName;
SET @SQLStmt = 'SELECT 1 FROM [' + @tableName + '] WHERE [' + @columnName + '] =''' + @SearchString + '''';
PRINT @SQLStmt;
EXEC sp_executesql @SQLStmt;
/* if the query returns any records, log the table and column containing the value. */
IF @@ROWCOUNT > 0
BEGIN
-- log the (tablename, columnname) to some table
INSERT INTO LogTable(tablename, columnname) VALUES (@tableName, @columnName);
END
-- fetch the next row
FETCH NEXT FROM tableCol_cursor INTO @columnName, @tableName;
END;
-- close and deallocate the cursor
CLOSE tableCol_cursor;
DEALLOCATE tableCol_cursor;
/* test the result... 2 rows */
SELECT *
FROM LogTable;
If there's a way of doing this without resorting to cursors and dynamic SQL, I don't know what it is. (It's okay, I won't be insulted if you smarties school me...)
December 1, 2025 at 3:45 pm
i have done something similar to what pietlinden as posted. mine is a procedure, but same concept, for my results go into a global temp table. it also limits to just columns that are as long or longer than the search term, and also only text tables.
i would call it with EXECUTE sp_uglysearch ' lowell@somesite.com', and since this is a huge stack of continuous queries that would take a long time, `, i would go to a new SSMS tab and do a simple query to see the progress so far.
SELECT * FROM ##Results
--desc: ugly search scans all string column types for a specific value
IF OBJECT_ID('[dbo].[sp_uglysearch]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_uglysearch];
GO
--#################################################################################################
-- Real World DBA Toolkit version 4.94 Lowell Izaguirre lowell@stormrage.com
--#################################################################################################
CREATE PROCEDURE [dbo].[sp_uglysearch]
--#################################################################################################
--DO NOT RUN THIS ON PRODUCTION
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: to search every string column in a databasefor a specific word
--DO NOT RUN THIS ON PRODUCTION
--this creates a cursor that loops through all th eobjects; on a MillionBillion row table,
--this might take forever due to the massive table scans.
--DO NOT RUN THIS ON PRODUCTION
--#################################################################################################
/*
SELECT object_schema_name(object_id),object_name(object_id),* FROM sys.columns s
WHERE TYPE_NAME(s.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (s.max_length >= LEN('Provisiona') --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR s.max_length=-1
--DO NOT RUN THIS ON PRODUCTION
--Purpose: to search every string column in a database for a specific word or phrase
--returns sql statement as a string which identifies the matching table
--usage:
-- EXEC sp_uglysearch 'Provisional'
-- EXEC sp_uglysearch 'TEST'
-- creates one SQL for each table that actually has a match for the searched value i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'
*/
@SEARCHSTRING VARCHAR(1024),@ClearPreviousResults INT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ISQL VARCHAR(MAX),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNs VARCHAR(MAX),
@COLZ VARCHAR(MAX);
IF OBJECT_ID('tempdb.[dbo].[##Results]') IS NULL
BEGIN
CREATE TABLE [dbo].[##Results] (
[SchemaName] NVARCHAR(128) NULL,
[TableName] sysname NOT NULL,
[type_desc] NVARCHAR(60) NULL,
[Columns] NVARCHAR(MAX) NULL,
[SQLCmd] NVARCHAR(MAX) NULL);
END;
ELSE
BEGIN
IF @ClearPreviousResults = 1
EXEC('TRUNCATE TABLE ##Results;');
END;
DECLARE [c2] CURSOR FOR
--###############################################################################################
--cursor definition
--###############################################################################################
SELECT DISTINCT
--schema_name(t.schema_id) As SchemaName,
[t].[name] AS [TableName],
--t.type_desc,
--sq.Columns,
'IF EXISTS(SELECT * FROM ' + QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) + ' WHERE ' + [sq].[Columns] + ')
BEGIN
PRINT ''SELECT * FROM ' + QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) + ' WHERE ' + REPLACE([sq].[Columns],'''','''''') + '''
INSERT INTO ##Results([SchemaName],[TableName],[type_desc],[Columns],[SQLCmd])
SELECT ''' + SCHEMA_NAME([t].[schema_id]) +''' As SchemaName,
''' + [t].[name] COLLATE Latin1_General_CI_AS + ''' AS TableName,
''' + [t].[type_desc] COLLATE Latin1_General_CI_AS + ''' AS type_desc,
''' + REPLACE([sq].[Columns],'''','''''') COLLATE Latin1_General_CI_AS + ''' AS Columns,
''SELECT * FROM ' + QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) + ' WHERE ' + REPLACE([sq].[Columns],'''','''''') + ''' AS SQLCmd
END ' AS [SQLCmd]
FROM [sys].[tables] AS [t]
JOIN (
SELECT.[object_id],
[Columns] = STUFF((SELECT ' OR ' + QUOTENAME([sc].[name])+ ' LIKE ''%' + @SEARCHSTRING + '%'' '
FROM [sys].[columns] AS [sc]
WHERE [sc].[object_id] =.[object_id]
--varchar(max) needs searching as well?
AND TYPE_NAME([sc].[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
(([sc].[max_length] / CASE WHEN TYPE_NAME([sc].[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR [sc].[max_length]=-1)
)
FOR XML PATH('')),1,4,'') --replace starting ' OR '
FROM [sys].[columns] AS
WHERE TYPE_NAME(.[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
((.[max_length] / CASE WHEN TYPE_NAME(.[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR.[max_length]=-1)
)
) AS [sq] ON [t].[object_id] = [sq].[object_id]
WHERE [t].[type_desc]='USER_TABLE'
AND [sq].[Columns] IS NOT NULL;
--###############################################################################################
OPEN [c2];
FETCH NEXT FROM [c2] INTO @TABLENAME,@ISQL;
WHILE @@fetch_status <> -1
BEGIN
--print @TABLENAME
--print '----'
--print @isql
EXEC(@ISQL);
FETCH NEXT FROM [c2] INTO @TABLENAME,@ISQL;
END;
CLOSE [c2];
DEALLOCATE [c2];
SELECT * FROM [##Results] ORDER BY [TableName];
END; --PROC
GO
Lowell
December 3, 2025 at 12:49 am
??
I'm confused. I copied and pasted your code and tried to run it. It returns this error:
Msg 156, Level 15, State 1, Procedure sp_uglysearch, Line 84 [Batch Start Line 7]
Incorrect syntax near the keyword 'WHERE'.
Msg 102, Level 15, State 1, Procedure sp_uglysearch, Line 86 [Batch Start Line 7]
Incorrect syntax near '.'.'
These are lines 84 and following:
AND TYPE_NAME([sc].[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
(([sc].[max_length] / CASE WHEN TYPE_NAME([sc].[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR [sc].[max_length]=-1)Oh wait... maybe this is it?
Oh wait... maybe this is it?
SELECT ' OR ' + QUOTENAME([sc].[name])+ ' LIKE ''%' + @SEARCHSTRING + '%'' '
FROM [sys].[columns] AS [sc]
WHERE [sc].[object_id] = .[object_id]
What object is [sys].[columns] joining to?
December 3, 2025 at 1:25 am
original code has been subject to an incorrect replace somehow.
but it should have been easy for you to identify where an alias is missing and which columns refer to it (all start with a . (dot))
-- the subselect below was missing an alias "([columns] as " - and all columns without an alias before the . should be given that same alias
FROM [sys].[tables] AS [t]
JOIN (
SELECT c.[object_id],
[Columns] = STUFF((SELECT ' OR ' + QUOTENAME([sc].[name])+ ' LIKE ''%' + @SEARCHSTRING + '%'' '
FROM [sys].[columns] AS [sc]
WHERE [sc].[object_id] = c.[object_id]
--varchar(max) needs searching as well?
AND TYPE_NAME([sc].[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
(([sc].[max_length] / CASE WHEN TYPE_NAME([sc].[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR [sc].[max_length]=-1)
)
FOR XML PATH('')),1,4,'') --replace starting ' OR '
FROM [sys].[columns] AS c
WHERE TYPE_NAME(c.[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
((c.[max_length] / CASE WHEN TYPE_NAME(c.[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR c.[max_length]=-1)
)
) AS [sq] ON [t].[object_id] = [sq].[object_id]
December 4, 2025 at 9:38 pm
Oh that! LOL
Thanks Frederico!
Apologies to the OP, rcrock, for sorta hijacking his thread.
Here's the fixed code:
--desc: ugly search scans all string column types for a specific value
IF OBJECT_ID('[dbo].[sp_uglysearch]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_uglysearch];
GO
--#################################################################################################
-- Real World DBA Toolkit version 4.94 Lowell Izaguirre lowell@stormrage.com
--#################################################################################################
CREATE PROCEDURE [dbo].[sp_uglysearch]
--#################################################################################################
--DO NOT RUN THIS ON PRODUCTION
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: to search every string column in a databasefor a specific word
--DO NOT RUN THIS ON PRODUCTION
--this creates a cursor that loops through all th eobjects; on a MillionBillion row table,
--this might take forever due to the massive table scans.
--DO NOT RUN THIS ON PRODUCTION
--#################################################################################################
/*
SELECT object_schema_name(object_id),object_name(object_id),* FROM sys.columns s
WHERE TYPE_NAME(s.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (s.max_length >= LEN('Provisiona') --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR s.max_length=-1
--DO NOT RUN THIS ON PRODUCTION
--Purpose: to search every string column in a database for a specific word or phrase
--returns sql statement as a string which identifies the matching table
--usage:
-- EXEC sp_uglysearch 'Provisional'
-- EXEC sp_uglysearch 'TEST'
-- creates one SQL for each table that actually has a match for the searched value i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'
*/@SEARCHSTRING VARCHAR(1024),@ClearPreviousResults INT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ISQL VARCHAR(MAX),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNs VARCHAR(MAX),
@COLZ VARCHAR(MAX);
IF OBJECT_ID('tempdb.[dbo].[##Results]') IS NULL
BEGIN
CREATE TABLE [dbo].[##Results] (
[SchemaName] NVARCHAR(128) NULL,
[TableName] sysname NOT NULL,
[type_desc] NVARCHAR(60) NULL,
[Columns] NVARCHAR(MAX) NULL,
[SQLCmd] NVARCHAR(MAX) NULL);
END;
ELSE
BEGIN
IF @ClearPreviousResults = 1
EXEC('TRUNCATE TABLE ##Results;');
END;
DECLARE [c2] CURSOR FOR
--###############################################################################################
--cursor definition
--###############################################################################################
SELECT DISTINCT
--schema_name(t.schema_id) As SchemaName,
[t].[name] AS [TableName],
--t.type_desc,
--sq.Columns,
'IF EXISTS(SELECT * FROM ' + QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) + ' WHERE ' + [sq].[Columns] + ')
BEGIN
PRINT ''SELECT * FROM ' + QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) + ' WHERE ' + REPLACE([sq].[Columns],'''','''''') + '''
INSERT INTO ##Results([SchemaName],[TableName],[type_desc],[Columns],[SQLCmd])
SELECT ''' + SCHEMA_NAME([t].[schema_id]) +''' As SchemaName,
''' + [t].[name] COLLATE Latin1_General_CI_AS + ''' AS TableName,
''' + [t].[type_desc] COLLATE Latin1_General_CI_AS + ''' AS type_desc,
''' + REPLACE([sq].[Columns],'''','''''') COLLATE Latin1_General_CI_AS + ''' AS Columns,
''SELECT * FROM ' + QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) + ' WHERE ' + REPLACE([sq].[Columns],'''','''''') + ''' AS SQLCmd
END ' AS [SQLCmd]
-- the subselect below was missing an alias "([columns] as " - and all columns without an alias before the . should be given that same alias
FROM [sys].[tables] AS [t]
JOIN (
SELECT c.[object_id],
[Columns] = STUFF((SELECT ' OR ' + QUOTENAME([sc].[name])+ ' LIKE ''%' + @SEARCHSTRING + '%'' '
FROM [sys].[columns] AS [sc]
WHERE [sc].[object_id] = c.[object_id]
--varchar(max) needs searching as well?
AND TYPE_NAME([sc].[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
(([sc].[max_length] / CASE WHEN TYPE_NAME([sc].[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR [sc].[max_length]=-1)
)
FOR XML PATH('')),1,4,'') --replace starting ' OR '
FROM [sys].[columns] AS c
WHERE TYPE_NAME(c.[system_type_id]) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND (
((c.[max_length] / CASE WHEN TYPE_NAME(c.[system_type_id]) IN ('NVARCHAR','NCHAR') THEN 2 ELSE 1 END ) >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
OR c.[max_length]=-1)
)
) AS [sq] ON [t].[object_id] = [sq].[object_id]
WHERE [t].[type_desc]='USER_TABLE'
AND [sq].[Columns] IS NOT NULL;
--###############################################################################################
OPEN [c2];
FETCH NEXT FROM [c2] INTO @TABLENAME,@ISQL;
WHILE @@fetch_status <> -1
BEGIN
--print @TABLENAME
--print '----'
--print @isql
EXEC(@ISQL);
FETCH NEXT FROM [c2] INTO @TABLENAME,@ISQL;
END;
CLOSE [c2];
DEALLOCATE [c2];
SELECT * FROM [##Results] ORDER BY [TableName];
END; --PROC
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply