SQL: search for a string in every varchar column in a database
Using the technique found here the following script generates SELECT's for all ((n)var)char columns in the given database. Copy/paste the output, remove the very last 'union' and execute.. You'll need to replace MISSPELLING HERE with the string you're looking for.
select
'select distinct ''' + tab.name + '.' + col.name
+ ''' from [' + tab.name
+ '] where [' + col.name + '] like ''%MISSPELLING HERE%'' union '
from sys.tables tab
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id)
where tab.type_desc ='USER_TABLE'
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');
Find a string by searching all tables in SQL Server
If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.
Of course depending on your sql server instance, it must support table variables.
I also added a USE statement to narrow the search scope
USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
Search for a string in all tables, rows and columns of a DB
This code should do it in SQL 2005, but a few caveats:
It is RIDICULOUSLY slow. I tested it on a small database that I have with only a handful of tables and it took many minutes to complete. If your database is so big that you can't understand it then this will probably be unusable anyway.
I wrote this off the cuff. I didn't put in any error handling and there might be some other sloppiness especially since I don't use cursors often. For example, I think there's a way to refresh the columns cursor instead of closing/deallocating/recreating it every time.
If you can't understand the database or don't know where stuff is coming from, then you should probably find someone who does. Even if you can find where the data is, it might be duplicated somewhere or there might be other aspects of the database that you don't understand. If no one in your company understands the database then you're in a pretty big mess.
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_schema SYSNAME,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'Test'
DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL -- Only strings have this and they always have it
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
END
CLOSE tables_cur
DEALLOCATE tables_cur
How to find a string inside a entire database?
This will work:
DECLARE @MyValue NVarChar(4000) = 'something';
SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
sys.tables T ON S.schema_id = T.schema_id;
WHILE (EXISTS (SELECT * FROM #T)) BEGIN
DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
DECLARE @TableName NVarChar(1000) = (
SELECT TOP 1 SchemaName + '.' + TableName FROM #T
);
SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);
DECLARE @Cols NVarChar(4000) = '';
SELECT
@Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
FROM sys.columns C
WHERE C.object_id = OBJECT_ID(@TableName);
SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
SELECT @SQL = @SQL + @Cols;
EXECUTE(@SQL);
DELETE FROM #T
WHERE SchemaName + '.' + TableName = @TableName;
END;
DROP TABLE #T;
A couple caveats, though. First, this is outrageously slow and non-optimized. All values are being converted to nvarchar simply so that they can be compared without error. You may run into problems with values like datetime not converting as expected and therefore not being matched when they should be (false negatives).
The WHERE (0 = 1) is there to make building the OR clause easier. If there are not matches you won't get any rows back.
SQL Server : Search for a string across all the databases and list all the databases, tables and corresponding columns
You can use this stored procedure with value for search like this :
exec SearchAllDatabases @SearchTerm = '%B2%'
This can help you to search everywhere but if you have too much data in your databases this will take time to execute.
Stored procedure :
CREATE PROCEDURE dbo.SearchAllDatabases
@SearchTerm NVARCHAR(255) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF @SearchTerm IS NULL OR @SearchTerm NOT LIKE N'%[^%^_]%'
BEGIN
RAISERROR(N'Please enter a valid search term.', 11, 1);
RETURN;
END
CREATE TABLE #results
(
[database] SYSNAME,
[schema] SYSNAME,
[table] SYSNAME,
[column] SYSNAME,
ExampleValue NVARCHAR(1000)
);
DECLARE
@DatabaseCommands NVARCHAR(MAX) = N'',
@ColumnCommands NVARCHAR(MAX) = N'';
SELECT @DatabaseCommands = @DatabaseCommands + N'
EXEC ' + QUOTENAME(name) + '.sys.sp_executesql
@ColumnCommands, N''@SearchTerm NVARCHAR(MAX)'', @SearchTerm;'
FROM sys.databases
WHERE database_id > 4 -- non-system databases
AND[state] = 0-- online
AND user_access = 0; -- multi-user
SET @ColumnCommands = N'DECLARE @q NCHAR(1),
@SearchCommands NVARCHAR(MAX);
SELECT @q = NCHAR(39),
@SearchCommands = N''DECLARE @VSearchTerm VARCHAR(255) = @SearchTerm;'';
SELECT @SearchCommands = @SearchCommands + CHAR(10) + N''
SELECT TOP(1)
[db] = DB_NAME(),
[schema] = N'' + @q + s.name + @q + '',
[table] = N'' + @q + t.name + @q + '',
[column] = N'' + @q + c.name + @q + '',
ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 1000)
FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE
WHEN c.system_type_id IN(35, 167, 175) THEN ''V''
ELSE '''' END + ''SearchTerm;''
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
AND c.max_length >= LEN(@SearchTerm);
PRINT @SearchCommands;
EXEC sys.sp_executesql @SearchCommands,
N''@SearchTerm NVARCHAR(255)'', @SearchTerm;';
INSERT #Results
(
[database],
[schema],
[table],
[column],
ExampleValue
)
EXEC[master].sys.sp_executesql @DatabaseCommands,
N'@ColumnCommands NVARCHAR(MAX), @SearchTerm NVARCHAR(255)',
@ColumnCommands, @SearchTerm;
SELECT[Searched for] = @SearchTerm;
SELECT[database],[schema],[table],[column],ExampleValue
FROM #Results
ORDER BY[database],[schema],[table],[column];
END
GO
I hope this helps you.
How to search all text fields in a DB for some substring with T-SQL
You'r close. Compare yours with this example: Searching and finding a string value in all columns in a SQL Server table
The above link is for searching a single table, however here is another link which includes all tables: How to search all columns of all tables in a database for a keyword?
EDIT : Just in case the link ever goes bad, here's the solution from that link...
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
EXEC SearchAllTables '<yourSubstringHere>'
Note:
As the comment suggests in the code snippet, it was tested using older versions of SQL Server. This may not work on SQL Server 2012.
SQL Server SELECT where any column contains 'x'
First Method(Tested)
First get list of columns in string variable separated by commas and then you can search 'foo' using that variable by use of IN
Check stored procedure below which first gets columns and then searches for string:
DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)
-----------------------------------------------------------------------
-- Set up the name of the table here :
SET @TABLE_NAME = 'testing'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'
-----------------------------------------------------------------------
DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)
IF @SCHEMA_NAME =''
BEGIN
PRINT 'Error : No schema defined!'
RETURN
END
IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
ON T.schema_id=S.schema_id
WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
BEGIN
PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
@SCHEMA_NAME+''' does not exist in this database!'
RETURN
END
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT CASE WHEN PATINDEX('% %',C.name) > 0
THEN '['+ C.name +']'
ELSE C.name
END
FROM sys.columns C
JOIN sys.tables T
ON C.object_id = T.object_id
JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE T.name = @TABLE_NAME
AND S.name = @SCHEMA_NAME
ORDER BY column_id
SET @vvc_ColumnList=''
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName
-- get the details of the next column
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName
-- add a comma if we are not at the end of the row
IF @@FETCH_STATUS=0
SET @vvc_ColumnList = @vvc_ColumnList + ','
END
CLOSE TableCursor
DEALLOCATE TableCursor
-- Now search for `foo`
SELECT *
FROM testing
WHERE 'foo' in (@vvc_ColumnList );
2nd Method
In sql server you can get object id of table then using that object id you can fetch columns. In that case it will be as below:
Step 1: First get Object Id of table
select * from sys.tables order by name
Step 2: Now get columns of your table and search in it:
select * from testing where 'foo' in (select name from sys.columns where object_id =1977058079)
Note: object_id is what you get fetch in first step for you relevant table
Related Topics
Sqlite Multi-Primary Key on a Table, One of Them Is Auto Increment
My Select Sum Query Returns Null. It Should Return 0
How to Check If Identity_Insert Is Set to on or Off in SQL Server
Combining Results of Two Select Statements
How to Use Non-Aggregate Columns with Group By
Is Bigint(8) the Largest Integer MySQL Can Store
What Is the Ms SQL Server Capability Similar to the MySQL Field() Function
Update Statement with Multiple Joins in Postgresql
SQL Server , Split a Time Duration Row by 24 Hour Period
Select Multiple Tables When One Table Is Empty in MySQL
Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server
Date/Timestamp to Record When a Record Was Added to the Table
Use String Contains Function in Oracle SQL Query
How to Edit Values of an Insert in a Trigger on SQL Server