Search of SQL Database

Question

I was wondering if it is possible to search for a string of data in an entire sql database?

i know you can search for certain column headers etc but I need to search for a single string of data and have no idea where in the database (table / column etc ) it will be held.

Is it possible? How would I do this in order to return the column, table or both for where it is held?

any advice much appreciated.

(I am using sql server management studio 2005)

I found [this](http://community.landesk.com/support/docs/DOC-2901). Hope it helps. — keyser, May 22 '12 at 15:30
My best suggestion would be to turn the tables into HTML and to search through there for the data you are looking for. I think you are clear in your question that you want to search data and not metadata. — Gordon Linoff, May 22 '12 at 15:34
Thanks for your directions to other sources. have sorted the issue now. — user1086159, May 22 '12 at 16:15

score 0 · Answer 1 · answered May 22 '12 at 15:32

0

You could write a script for that starting with

select *, COLUMN_NAME, TABLE_NAME 
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'your_schema'

With that list of tables and columns you can iterate over all of them looking for your input.

answered May 22 '12 at 15:32

juergen d

201,996
37
293
362

score 0 · Answer 2 · edited Mar 04 '15 at 11:16

This is the script I used in the end. very straight forwards and a great little thing to know.

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 



exec FindMyData_string 'VBAR2', 0

Search of SQL Database

2 Answers2