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

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 = 0ASSET NOCOUNT ONDECLARE @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 INTDECLARE @MAX INTDECLARE @TableName sysnameDECLARE @ColumnName sysnameDECLARE @SchemaName sysnameDECLARE @SQL NVARCHAR(4000)DECLARE @PARAMETERS NVARCHAR(4000)DECLARE @DataExists BITDECLARE @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 = 1SELECT @i = 1, @MAX = MAX(RowId)FROM   @TempWHILE @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    ENDSELECT  SchemaName,TableName, ColumnNameFROM    @TempWHERE   DataFound = 1GO

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:

selectS.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_idinner join sys.syscomments C on O.object_id = C.idwhere S.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemasand C.text like '%ICE_%'order by [Schema]