We help IT Professionals succeed at work.

How to: TSQL search all columns in a table?

Tom Sage
Tom Sage asked
on
What is the best way to do an exact, or LIKE, search in ALL columns of a table?

Thank you
Comment
Watch Question

Commented:
hi
u can find column of table by join sys.columns and sys.tables on object_id
James MurrellProduct Specialist

Commented:
this is something i refer to many times http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

hope it helps

james

Author

Commented:
James,

I would prefer a solution that worked on only one table, but I will check it out.

THanks

Commented:
one table??????
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='yourtablename'
James MurrellProduct Specialist

Commented:

you could try

SELECT * FROM myTable WHERE CONTAINS (myCol1, myCol2, "myString")
More details about CONTAINS at http://msdn2.microsoft.com/library/ms187787.aspx

taken from http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/9b38d03c-4619-4e99-b1cf-948f37ddb000

Author

Commented:
pbehin,

In your example, where is the search string?

Thanks

Author

Commented:
James,

I tried an example using CONTAINS and it says I have to enable full-text index.

I will do that and then try CONTAINS again.

Thanks
Top Expert 2012

Commented:
>>I will do that and then try CONTAINS again.<<
Full-Text search is a lot more involved than that.  If you want to pursue that approach, I can guide you, but please read up on it first.  It may be more than you want/need.

Author

Commented:
Mr Perkins,

I think you may have a point.  I really do not want to change the indexing I am using now.  Maybe it would be easier to just build a program that would loop through the columns doing a search on each one.   The table is only about 4000 records so it would not take too long.

Thank you for your feedback.  If you want to provide further help, I can open a new question so you can get points.

Thanks
James MurrellProduct Specialist

Commented:
it undocumented but sp_MSForEachTable may help

Top Expert 2012

Commented:
>>Maybe it would be easier to just build a program that would loop through the columns doing a search on each one.<<
All you have to do is modify the code that was posted here http:#a32964250, so that it only searches the one table.  It cannot be too difficult.
C# ASP.NET Developer
Top Expert 2010
Commented:
If you are trying to get the data on a row by row basis, and not just return the results of data in the table, I wrote a routine that will build the query condition which can be used. Below is a sample. What it does is use the tablename to determine which columns can be searched, and builds a PATINDEX condition for each field. The result looks something like this:

PATINDEX('%x%', TestData1) > 0 OR PATINDEX('%x%', TestData2) > 0 OR PATINDEX('%x%', TestData5) > 0 OR PATINDEX('%x%', TestData6) > 0
--

CREATE Table TestData(
 ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
 TestData1 CHAR(10),
 TestData2 VARCHAR(20),
 TestData3 INT,
 TestData4 DATETIME,
 TestData5 NCHAR(10),
 TestData6 NVARCHAR(MAX)
)

INSERT INTO TestData
SELECT 'abc', 'def', 1, GETDATE(), 'xyz', 'a1b2c3x4' UNION ALL
SELECT 'ghi', 'xat', 1, GETDATE(), 'fgh', 'e8f8f' UNION ALL
SELECT 'add', 'yes', 1, GETDATE(), 'foo', 'bar'

SELECT * FROM TestData
DECLARE @condition varchar(1000)
set @condition = dbo.RowContainsData('TestData', 'x')
select @condition

DECLARE @sqlexec VARCHAR(1000)
SET @sqlexec = 'SELECT * FROM TestData WHERE ' + @condition

EXEC (@sqlexec)
CREATE FUNCTION RowContainsData (
  @TableName SYSNAME,
  @SearchData VARCHAR(100)
) RETURNS VARCHAR(1000) AS BEGIN

  DECLARE @count INT
  DECLARE @ctr   INT
  DECLARE @ColName SYSNAME
  DECLARE @sql     VARCHAR(200)
  DECLARE @sqlExec VARCHAR(2000)

  DECLARE @Cols TABLE (
    ID         INT IDENTITY(1, 1),
    ColumnName NVARCHAR(128) 
  )
  
  INSERT INTO @Cols 
  SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_NAME = @TableName 
     AND Data_Type IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR')

  SET @sql = ''
  SELECT @count = COUNT(*) FROM @Cols
  SET @ctr = 1
  WHILE @ctr <= @count BEGIN
    SELECT @ColName  = ColumnName
     FROM @Cols
     WHERE ID = @ctr
     IF @ctr > 1 BEGIN
       SET @sql = @sql + ' OR '
     END
     SET @sql = @sql + REPLACE(REPLACE('PATINDEX(''%@@searchData@@%'', @@colName@@) > 0', '@@searchData@@', 
                                       @SearchData), '@@colName@@', @colName)
    SET @ctr = @ctr + 1
  END
  RETURN @sql
END

Open in new window

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Here is a screenshot of the results.
Results.PNG

Author

Commented:
Thank you for the fine solution !