none
How to find a value in all the columns of table? RRS feed

  • Question

  • I have to search a value: 20.281000 in all the columns of my table. Is there any way to search all the columns?

    Monday, March 26, 2012 10:57 AM

Answers

All replies

  • this help u :

    DECLARE @SearchStr nvarchar(100)
    	SET @SearchStr = 'aalbano'
    	
    	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')
    					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

    Monday, March 26, 2012 11:02 AM
  • Tried this code from this link, but it is not finding:

    Search every table and field in a SQL Server Database Updated

    Monday, March 26, 2012 11:08 AM
  • Try these two links @ http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

    Also a similar kind of script by Naomi @ http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu


    Monday, March 26, 2012 11:13 AM
  • Please follow http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, March 26, 2012 11:38 AM
  • Do you want to search for this number as numerical or as a character value? I have two blogs on this topic - one to search a character value and one to search an integer value. If you want to search for a number, then I suggest to start from the second blog and just modify it accordingly to allow search for decimal/numerical/float/money http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    • Marked as answer by KJian_ Monday, April 2, 2012 1:48 AM
    • Edited by Naomi NModerator Wednesday, July 9, 2014 9:17 PM
    Monday, March 26, 2012 3:05 PM
    Moderator
  • Saturday, March 31, 2012 11:32 PM
    Moderator
  • hi Use this
    Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000), @SN as varchar(200), @Exact_Match bit
    Create Table #myTable (Table_Name varchar(200), Column_Name varchar(200), Number_Of_Rows int)
     
    -- REPLACE @MYVALUE WITH THE VALUE YOU'RE SEARCHING FOR IN THE DATABASE
    Set @myValue = 'B13-001' 
    Set @Exact_Match = 1    
     
    Declare myCursor Cursor For
    Select T.Table_Name, C.Column_Name, T.Table_Schema
    From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C 
    On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
    Where T.Table_Name <> 'dtproperties' And Table_Type = 'Base Table'
    And C.Data_Type In ('varchar','char','nvarchar','nchar','sql_variant')
    Open myCursor
    Fetch Next From myCursor Into @TN, @CN, @SN
    While @@Fetch_Status <> -1
    Begin
    	If @Exact_Match = 0
    		Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] Like ''%' + @myValue + '%'''
    	    Else
    		Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] = ''' + @myValue + ''''
    	--Print @SQL
    	Exec sp_executesql @SQL 
    	Fetch Next From myCursor Into @TN, @CN, @SN
    End
    Close myCursor
    Deallocate myCursor
    Select * From #myTable Where Number_Of_Rows > 0 Order By Table_Name
    Drop Table #myTable


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Sunday, April 1, 2012 5:19 AM
  • CREATE PROCEDURE FindMyData_Date
        @DataToFind DATETIME
    AS
    SET NOCOUNT ON
     
    DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
     
    DECLARE @ISDATE 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 = 'DateTime'
                Or (Data_Type = 'SmallDateTime' And @DataToFind >= '19000101' And @DataToFind < '20790607'))
     
    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 = 'If Exists(Select *
                                    From   ReplaceTableName
                                    Where  [ReplaceColumnName]
                                                 = ''' + CONVERT(VARCHAR(30), @DataToFind, 126) + '''
                                    )
                              Set @DataExists = 1
                          Else
                              Set @DataExists = 0',
            @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

    Sunday, April 1, 2012 8:46 AM
  • You can also use my script with the settings:
    SET @SearchStrColumnValue = '20.281000'
    SET
    @SearchStrTableName = 'TableName'
    Wednesday, May 22, 2013 1:07 PM
  • link to the specific blog does not work.  

    (looks like we may have the latter)

    Wednesday, July 9, 2014 8:30 PM
  • I checked the link and updated the reference. You may also find my last TechNet article on this topic helpful

    Searching All Columns in a Table for a String


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 9, 2014 9:19 PM
    Moderator