SQL select for all records that may holds specific value

How to select all records,that may contain specific value that is known, without referring to specific column开发者_如何学C in SQL expression?

For instance, i know,that some unknown column holds value 'xxx' and there are many columns and records in table.

Thank you.

---

---

So, you want to do a Google-like free text search over your database. This can be done but the performance will be Teh Suck! Google is fast because it has indexes on its indexes, duplicate data stores and generally optimizes everything for precisely this kind of search.

Anyway, here is a proof of concept using dynamic SQL and the Oracle data dictionary. Note that I restrict the columns to the type of data I want to search for i.e. strings.

SQL> set serveroutput on size unlimited
SQL> declare
  2      dummy varchar2(1);
  3  begin
  4      for r in ( select table_name, column_name from user_tab_cols
  5                 where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )
  6      loop
  7          begin
  8              execute immediate 'select null from '||r.table_name
  9                      ||' where '||r.column_name||' like ''%&search_value%'' '
 10                      ||' and rownum = 1'
 11                 into dummy;
 12              dbms_output.put_line('Found it in >>>'
 13                     ||r.table_name||'.'||r.column_name);
 14          exception
 15              when others then
 16                  -- bad practice ahoy!
 17                  null;
 18          end;
 19      end loop;
 20  end;
 21  /
Enter value for search_value: MAISIE
old   9:                ||' where '||r.column_name||' like ''%&search_value%'' '
new   9:                ||' where '||r.column_name||' like ''%MAISIE%'' '
Found it in >>>T23.NAME

PL/SQL procedure successfully completed.

SQL>

A more robust implementation might need to handle case, whole words, etc. If you're on 10g or higher then regular expressions could be useful, but combining regex and dynamic SQL is an, er, interesting prospect.

I repeat that performance is going to be Teh Suck! on a large data set. It is virtually impossible to tune, because we cannot index every column, and certainly not to support LIKE or similar fuzzy matches. An alternative approach would be to use XQuery to generate an XML representation of your data and then use Text to index it. Maintaining such a repository would be overhead, but the effort would be a sound investment if you need this functionality of a regular basis, especially in a production environment.

---

We can conduct a broader search across all the tables we have privileges on by using all_tab_cols instead.

for r in ( select owner, table_name, column_name from all_tab_cols
                   where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )

Obviously we need to prefix the owning schema in the generated statement.

execute immediate 'select null from '||r.owner||'.'||r.table_name
                       ||' where '||r.column_name||' like ''%

---

SELECT * FROM table WHERE column='xxx';

But if you have many columns which can contain this value, you need to use OR:

SELECT * FROM table WHERE column1='xxx' or column2='xxx' or column3='xxx';

---

If you cannot explicitly write all the possible columns, you should generate a dynamic SQL query using the schema metadata.

---

If you need to do this once or twice then APC's answer is good. If this is somehow (shudder) part of an ongoing requirement, then I think the best you'll be able to do is to create an Oracle computed field on the table or tables of interest and search on that. Use a delimiter that you're sure won't show up in the actual text values, e.g.:

alter table mytable add search_column 
 as (mycolumn1||'^'||mycolumn2||'^'||mycolumn3);

Now your query becomes something like:

select <whatever transformation you want to see here> 
from mytable where search_column like '%^xxx^%'

(That sound you may have just heard was Codd spinning in his grave)

---

select * from table_name where(Table_Attribute='XXX');

this will show you all records with attribute XXX

---

Run this to get desired result, sry for bad naming used.

declare @_var nvarchar(1000)
declare @var1 nvarchar(1000)
declare @var2 nvarchar(1000)
declare _cur cursor 
for select 

case Column_name 
            when '' then '' 
            else Column_name+'=''asd'' OR ' 
        end 
from information_schema.columns 
    where table_name='M_Patient' 
            and 
          data_type ='nvarchar'

open _cur
fetch _cur into @_var
while(@@fetch_status=0)
begin
set @var2=isnull(@var2,'')+@_var
fetch _cur into @_var

end
close _cur
deallocate _cur

set @var1='select * from M_Patient where '+ substring(@var2, 0,len(@var2)-2)

execute (@var1)

---

I usually use this script if I need to search for a value in a database and I don't know the table and/or column. Just set the @SearcStr parameter and push play. Maybe it can help you along.

DROP TABLE #Results

DECLARE @SearchStr nvarchar(100)
SET     @SearchStr = ''

CREATE TABLE 
    #Results(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON
DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
DECLARE @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