How do I search an SQL Server database for a string?

I know it's possible, but I don't know how.

I need to search an SQL Server database for all mentions of a specific string.

For example: I would like to search all tables, views, functions, stored procedures, ... for string "tblEmployes" (not data within the tables).

One of the reasons I need this is I would like to remove some extra data tables that are created, but I am afraid that they are maybe used somewhere in procedures or functions.

15 Answers

This will search every column of every table in a specific database. Create the stored procedure on the database that you want to search in.

The Ten Most Asked SQL Server Questions And Their Answers:

CREATE PROCEDURE FindMyData_String @DataToFind NVARCHAR(4000), @ExactMatch BIT = 0 AS SET NOCOUNT ON DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT) INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType) SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type FROM Information_Schema.Columns AS C INNER Join Information_Schema.Tables AS T ON C.Table_Name = T.Table_Name AND C.TABLE_SCHEMA = T.TABLE_SCHEMA WHERE Table_Type = 'Base Table' And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char') DECLARE @i INT DECLARE @MAX INT DECLARE @TableName sysname DECLARE @ColumnName sysname DECLARE @SchemaName sysname DECLARE @SQL NVARCHAR(4000) DECLARE @PARAMETERS NVARCHAR(4000) DECLARE @DataExists BIT DECLARE @SQLTemplate NVARCHAR(4000) SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1 THEN 'If Exists(Select * From ReplaceTableName Where Convert(nVarChar(4000), [ReplaceColumnName]) = ''' + @DataToFind + ''' ) Set @DataExists = 1 Else Set @DataExists = 0' ELSE 'If Exists(Select * From ReplaceTableName Where Convert(nVarChar(4000), [ReplaceColumnName]) Like ''%' + @DataToFind + '%'' ) Set @DataExists = 1 Else Set @DataExists = 0' END, @PARAMETERS = '@DataExists Bit OUTPUT', @i = 1 SELECT @i = 1, @MAX = MAX(RowId) FROM @Temp WHILE @i <= @MAX BEGIN SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName) FROM @Temp WHERE RowId = @i PRINT @SQL EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT IF @DataExists =1 UPDATE @Temp SET DataFound = 1 WHERE RowId = @i SET @i = @i + 1 END SELECT SchemaName,TableName, ColumnName FROM @Temp WHERE DataFound = 1 GO 

To run it, just do this:

exec FindMyData_string 'google', 0 

It works amazingly well!!!

If you need to find database objects (e.g. tables, columns, and triggers) by name - have a look at the free Redgate Software tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely free to use for any kind of use??

You can also try ApexSQL Search – it’s a free SSMS add-in similar to SQL Search.

If you really want to use only SQL you might want to try this script:

select S.name as [Schema], o.name as [Object], o.type_desc as [Object_Type], C.text as [Object_Definition] from sys.all_objects O inner join sys.schemas S on O.schema_id = S.schema_id inner join sys.syscomments C on O.object_id = C.id where S.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas and C.text like '%ICE_%' order by [Schema] 

You can export your database (if small) to your hard drive / desktop, and then just do a string search via a text search program or text editor.

For getting a table by name in SQL Server:

SELECT * FROM sys.Tables WHERE name LIKE '%Employees%' 

For finding a stored procedure by name:

SELECT name FROM sys.objects WHERE name = 'spName' 

To get all stored procedures related to a table:

----Option 1 SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%' ----Option 2 SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%tablename%' 

This code searching procedure and function but not search in table :)

SELECT name FROM sys.all_objects WHERE Object_definition(object_id) LIKE '%text%' ORDER BY name 

You could;

1. Script the database to a single file and search the file for tblEmployees using a text editor. In SQL Server Management Studio (SSMS), right click over the database and choose Generate Scripts.
2. Use SSMS 'View Dependencies' by right clicking over tblEmployees to see which other objects are dependent on it
3. Use a free third-party tool such as Redgate Software's SQL Search to search all database objects by name and content by keyword.

I was given access to a database, but not the table where my query was being stored in.

Inspired by @marc_s answer, I had a look at HeidiSQL which is a Windows program that can deal with MySQL, SQL Server, and PostgreSQL.

I found that it can also search a database for a string.

It will search each table and give you how many times it found the string per table!

This will search for a string over every database:

declare @search_term varchar(max) set @search_term = 'something' select @search_term = 'use ? SET QUOTED_IDENTIFIER ON select ''[''+db_name()+''].[''+c.name+''].[''+b.name+'']'' as [object], b.type_desc as [type], d.obj_def.value(''.'',''varchar(max)'') as [definition] from ( select distinct a.id from sys.syscomments a where a.[text] like ''%'+@search_term+'%'' ) a inner join sys.all_objects b on b.[object_id] = a.id inner join sys.schemas c on c.[schema_id] = b.[schema_id] cross apply ( select [text()] = a1.[text] from sys.syscomments a1 where a1.id = a.id order by a1.colid for xml path(''''), type ) d(obj_def) where c.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas and db_id() not in (1,2,3,4) -- avoid sys databases' if object_id('tempdb..#textsearch') is not null drop table #textsearch create table #textsearch ( [object] varchar(300), [type] varchar(300), [definition] varchar(max) ) insert #textsearch exec sp_MSforeachdb @search_term select * from #textsearch order by [object] 

My version...

I named it "Needle in the haystack" for obvious reasons.

It searches for a specific value in each row and each column, not for column names, etc.

Execute search (replace values for the first two variables of course):

DECLARE @SEARCH_DB VARCHAR(100)='REPLACE_WITH_YOUR_DB_NAME' DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%REPLACE_WITH_SEARCH_STRING%' SET NOCOUNT ON; DECLARE col_cur CURSOR FOR SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime'); DECLARE @TOTAL int = (SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime')); DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500); DECLARE @SQL nvarchar(4000)=''; PRINT '-------- BEGIN SEARCH --------'; OPEN col_cur; FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE; BEGIN TRY DROP TABLE ##RESULTS; END TRY BEGIN CATCH END CATCH CREATE TABLE ##RESULTS( TABLE_CATALOG nvarchar(500), TABLE_SCHEMA nvarchar(500), TABLE_NAME nvarchar(500), COLUMN_NAME nvarchar(500), DATA_TYPE nvarchar(500), RECORDS int) DECLARE @SHOULD_CAST bit=0 DECLARE @i int =0 DECLARE @progress_sum bigint=0 WHILE @@FETCH_STATUS = 0 BEGIN -- PRINT '' + CAST(@i as varchar(100)) +' of ' + CAST(@TOTAL as varchar(100)) + ' ' + @TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME+': '+@COLUMN_NAME+' ('+@DATA_TYPE+')'; SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE WHEN 'varchar' THEN 0 WHEN 'nvarchar' THEN 0 WHEN 'char' THEN 0 ELSE 1 END) SET @SQL='SELECT '''+@TABLE_CATALOG+''' catalog_name, '''+@TABLE_SCHEMA+''' schema_name, '''+@TABLE_NAME+''' table_name, '''+@COLUMN_NAME+''' column_name, '''+@DATA_TYPE+''' data_type, ' + +' COUNT(['+@COLUMN_NAME+']) records '+ +' FROM '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME + +' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['+@COLUMN_NAME + '] as NVARCHAR(max)) ' ELSE ' ['+@COLUMN_NAME + '] ' END +' LIKE '''+ @SEARCH_VALUE_LIKE + ''' ' -- PRINT @SQL; IF @i % 100 = 0 BEGIN SET @progress_sum = (SELECT SUM(RECORDS) FROM ##RESULTS) PRINT CAST (@i as varchar(100)) +' of ' + CAST(@TOTAL as varchar(100)) +': '+ CAST (@progress_sum as varchar(100)) END INSERT INTO ##RESULTS (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, RECORDS) EXEC(@SQL) FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE; SET @i=@i+1 -- IF @i > 1000 -- BREAK END CLOSE col_cur; DEALLOCATE col_cur; SELECT * FROM ##RESULTS WHERE RECORDS>0; 

Then to view results, even while executing, from another window, execute:

DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%@FLEX@%' SELECT * FROM ##RESULTS WHERE RECORDS>0; SET NOCOUNT ON; DECLARE col_cur CURSOR FOR SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM ##RESULTS WHERE RECORDS>0; DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500); DECLARE @SQL nvarchar(4000)=''; OPEN col_cur; FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE; DECLARE @i int =0 DECLARE @SHOULD_CAST bit=0 WHILE @@FETCH_STATUS = 0 BEGIN SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE WHEN 'varchar' THEN 0 WHEN 'nvarchar' THEN 0 WHEN 'char' THEN 0 ELSE 1 END) SET @SQL='SELECT '''+@TABLE_CATALOG+''' catalog_name, '''+@TABLE_SCHEMA+''' schema_name, '''+@TABLE_NAME+''' table_name, '''+@COLUMN_NAME+''' column_name, '''+@DATA_TYPE+''' data_type, ' + +' ['+@COLUMN_NAME+']'+ +', * ' +' FROM '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME + +' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['+@COLUMN_NAME + '] as NVARCHAR(max)) ' ELSE ' ['+@COLUMN_NAME + '] ' END +' LIKE '''+ @SEARCH_VALUE_LIKE + ''' ' PRINT @SQL; EXEC(@SQL) FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE; SET @i=@i+1 -- IF @i > 10 -- BREAK END CLOSE col_cur; DEALLOCATE col_cur; 

Few mentions about it:

• it uses cursors instead of a blocking while loop
• it can print progress (uncomment if needed)
• it can exit after a few attempts (uncomment the IF at the end)
• it displays all records
• you can fine tune it as needed

DISCLAIMERS:

• DO NOT run it in production environments!
• It is slow. If the DB is accessed by other services/users, please add " WITH (NOLOCK) " after every table name in all the selects, especially the dynamic select ones.
• It does not validate/protect against all sorts of SQL injection options.
• If your DB is huge, prepare yourself for some sleep, make sure the query will not be killed after a few minutes.
• It casts some values to string, including ints/bigints/smallints/tinyints. If you don't need those, put them at the same exclusion lists with the timestamps at the top of the script.

The content of all stored procedures, views and functions are stored in field text of table sysComments. The name of all objects are stored in table sysObjects and the columns are in sysColumns.

Having this information, you can use this code to search in content of views, stored procedures, and functions for the specified word:

Select b.name from syscomments a inner join sysobjects b on a.id = b.id where text like '%tblEmployes%' 

This query will give you the objects which contains the word "tblEmployes" .

To search by the name of Objects you can use this code:

Select name from sysobjects where name like '%tblEmployes%' 

And finally to find the objects having at least one column containing the word "tblEmployes", you can use this code:

Select b.name from syscolumns a inner join sysobjects b on a.id = b.id where a.name like '%tblEmployes%' 

You can combine these three queries with union:

Select distinct b.name from syscomments a inner join sysobjects b on a.id = b.id where text like '%tblEmployes%' union Select distinct name from sysobjects where name like '%tblEmployes%' union Select distinct b.name from syscolumns a inner join sysobjects b on a.id = b.id where a.name like '%tblEmployes%' 

With this query you have all objects containing the word "tblEmployes" in content or name or as a column.

If I want to find where anything I want to search is, I use this:

DECLARE @search_string varchar(200) SET @search_string = '%myString%' SELECT DISTINCT o.name AS Object_Name, o.type_desc, m.definition FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like @search_string; 

Here is the same script as submitted by user l--''''''---------'''''''''''', but corrected to work on a case-sensitive SQL instance, and with some other minor improvements.

DROP PROCEDURE IF EXISTS dbo.spFind_Text_In_Database GO CREATE PROCEDURE dbo.spFind_Text_In_Database @strText_To_Find NVARCHAR(4000), @bitExact_Match BIT = 0 AS SET NOCOUNT ON DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT) INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType) SELECT C.TABLE_NAME, C.TABLE_SCHEMA, C.COLUMN_NAME, C.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS AS C INNER Join INFORMATION_SCHEMA.TABLES AS T ON C.TABLE_NAME = T.TABLE_NAME AND C.TABLE_SCHEMA = T.TABLE_SCHEMA WHERE TABLE_TYPE = 'BASE TABLE' And DATA_TYPE In ('ntext','text','nvarchar','nchar','varchar','char') DECLARE @i INT DECLARE @MAX INT DECLARE @TableName sysname DECLARE @ColumnName sysname DECLARE @SchemaName sysname DECLARE @SQL NVARCHAR(4000) DECLARE @PARAMETERS NVARCHAR(4000) DECLARE @DataExists BIT DECLARE @SQLTemplate NVARCHAR(4000) SELECT @SQLTemplate = CASE WHEN @bitExact_Match = 1 THEN 'If Exists(Select * From ReplaceTableName Where Convert(nVarChar(4000), [ReplaceColumnName]) = ''' + @strText_To_Find + ''' ) Set @DataExists = 1 Else Set @DataExists = 0' ELSE 'If Exists(Select * From ReplaceTableName Where Convert(nVarChar(4000), [ReplaceColumnName]) Like ''%' + @strText_To_Find + '%'' ) Set @DataExists = 1 Else Set @DataExists = 0' END, @PARAMETERS = '@DataExists Bit OUTPUT', @i = 1 SELECT @i = 1, @MAX = MAX(RowId) FROM @Temp WHILE @i <= @MAX BEGIN SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName) FROM @Temp WHERE RowId = @i PRINT @SQL EXEC sp_executesql @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT IF @DataExists =1 UPDATE @Temp SET DataFound = 1 WHERE RowId = @i SET @i = @i + 1 END SELECT SchemaName,TableName, ColumnName FROM @Temp WHERE DataFound = 1 GO 

Searching SQL Database objects is possible with SQL Server Management Studio (SSMS) with the following methods, with SSMS Object Search: object explorer details or T-SQL scripts as explained in following:

• Different ways to search for SQL Server database objects
• SQL Server Find Anything in Object Explorer in SSMS
• Search text with wildcards

Here is how you can search the database in Swift using the FMDB library.

First, go to this link and add this to your project: FMDB. When you have done that, then here is how you do it. For example, you have a table called Person, and you have firstName and secondName and you want to find data by first name, here is a code for that:

 func loadDataByfirstName(firstName : String, completion: @escaping CompletionHandler){ if isDatabaseOpened { let query = "select * from Person where firstName like '\(firstName)'" do { let results = try database.executeQuery(query, values: [firstName]) while results.next() { let firstName = results.string(forColumn: "firstName") ?? "" let lastName = results.string(forColumn: "lastName") ?? "" let newPerson = Person(firstName: firstName, lastName: lastName) self.persons.append(newPerson) } completion(true) }catch let err { completion(false) print(err.localizedDescription) } database.close() } } 

Then in your ViewController you will write this to find the person detail you are looking for:

 override func viewWillAppear(_ animated: Bool) { super.viewWillAppear(animated) SQLManager.instance.openDatabase { (success) in if success { SQLManager.instance.loadDataByfirstName(firstName: "Hardi") { (success) in if success { // You have your data Here } } } } }