SQL query like filter

I need to execute a search query in SQL Server where I need to filter out data based upon an user input textfield. The problem is, this query needs to be executed on several tables (so I only know the tablecolumns at runtime).

This is the query I have:

 SELECT *  FROM [BTcege开发者_C百科ka.C2M].[dbo].[Lookup_Country] WHERE Name LIke '%test%'

Now the problem is I need to do the Like function on every column (I only know the columnname at runtime) in the table. I am calling this query from an ASP.NET website. The user selects a table from a dropdownlist and can then enter the search field.

This is what I really want to accomplish:

SELECT *  FROM [BTcegeka.C2M].[dbo].[Lookup_Country] WHERE * LIke '%test%'

Obviously 'Where * Like' Fails. How can I accomplish this?

You can query all columns in a table like:

select name from sys.columns where object_id = object_id('YourTable')

Then you can construct a query that does a like for each column.

Another approach is to create a calculated column called SearchField that contains a concatenation of all strings you'd like to search for. Then you can search like:

create table #tmp (id int identity, col1 varchar(10), col2 varchar(10), 
    SearchField as col1 + '|' + col2 persisted)
insert #tmp (col1, col2) values 
    ('alfa', 'beta'), 
    ('gamma', 'DELTA'),
    ('GAMMA', 'delta')

select * from #tmp where SearchField like '%alfa%'

Try using your SQL query like this.

SELECT * FROM [BTcegeka.C2M].[dbo].[Lookup_Country] WHERE COL1 LIke '%test%' OR COL2 LIke '%test%' OR COL3 LIke '%test%'

You may use AND instead of OR if your requirement needs that.

If you know the column names at run time, then you should build you query in .NET before passing it to sql. You can build it with the correct column name. This way you can account also for the type of the column you search in. Careful though this path you chose is prone to SQL injection so before sending a query to the SQL you should check it.

If you really need to do this you can search in sqlserver meta tables and find the description of selected user table. Make a good use of this data is easy and you can make any sql you want with this information, but performance may not the that good

you have to use dynamic sql for implementing this. Your column name needs to be passed as parameter to this stored procedure or if you dont want to create stored procedure just declare one paramter and assign the value selected from the drop down list to it and use that in the query.

 create procedure sp_dynamicColumn
  (
   @columnName varchar(10)
  )
  as
  begin
  declare @DYNAMICSQL nvarchar(4000);
  SET @DYNAMICSQL = 'Select * from [BTcegeka.C2M].[dbo].[Lookup_Country] where '+ @columnName +  ' like ''%test%'''
  EXECUTE SP_EXECUTESQL @DYNAMICSQL
  end
  go