Query to search in all columns in table with joining

Question

I am searching in all column data in the selected SQL table, and I have a join with another table, but my issue that the 2nd table has the same column name with tableA, how can I specify each column with its table. So I declare the string to find and the table, if I remove the join it will work, but my issue only with joining

DECLARE @stringToFind varchar(max) ='%TELECOM%'
DECLARE @table varchar(max) ='tableA'
DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @table + '] 
Left join    Table2 on
               Table2.Id= tableA.IdCategory  
WHERE ' 
SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = @table 
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
EXEC (@sqlCommand)
PRINT @sqlCommand

Ambiguous column name 'Type'.

score 0 · Answer 1 · answered Jan 29 '21 at 11:17

0

"Select *" will return all columns from both joined tables. If both tables have a column with the same name it will be ambiguous.

Table qualifiers may help. Explicitly select the columns you want.

select T1.type atype,T2.type another_type, T1.*,T2.* from Table1 as T1 left join Table2 as T2 on T1.columnName= T2.columnName

answered Jan 29 '21 at 11:17

TonZ

46
3

I know how to do it, but the issue with this query : + '[' + COLUMN_NAME + '] – Med Amin Jan 29 '21 at 11:41
see the 2nd Select – Med Amin Jan 29 '21 at 11:44

score 0 · Answer 2 · answered Jan 29 '21 at 11:43

If performance does not matter, there is a fully generic approach implying XML XQuery.

The following will search through all attributes in the generated XML using contains(), which is quite the same as LIKE as you are using it in your sample.

DECLARE @stringToFind varchar(max)='owner';

SELECT
(
    SELECT *
    FROM sys.objects o
    inner join sys.columns c ON c.object_id=o.object_id
    for xml auto,type
).query('//*[@*[contains(.,sql:variable("@stringToFind"))]]');

In my case the string "owner" is found in one table's attribute as well as in several column's attributes.

The result in my case looks like this (shortend):

<c object_id="5" name="ownertype" column_id="2" system_type_id="48"
<c object_id="7" name="ownerid" column_id="3" system_type_id="127" 
<o name="sysowners" object_id="27" schema_id="4" parent_object_id="
  <c object_id="27" name="id" column_id="1" system_type_id="56" use
  <c object_id="27" name="name" column_id="2" system_type_id="231" 
  <c object_id="27" name="type" column_id="3" system_type_id="175" 
  <c object_id="27" name="sid" column_id="4" system_type_id="165" u
  <c object_id="27" name="password" column_id="5" system_type_id="1
  <c object_id="27" name="dfltsch" column_id="6" system_type_id="23
  <c object_id="27" name="status" column_id="7" system_type_id="56"
  <c object_id="27" name="created" column_id="8" system_type_id="61
  <c object_id="27" name="modified" column_id="9" system_type_id="6
  <c object_id="27" name="deflanguage" column_id="10" system_type_i
</o>

The idea in short:

Using FOR XML AUTO will created XML elements with the tables aliases as tags, while the attributes are the values.
The XQuery expressions will look through any attribute anywhere in the XML.
The predicate will filter for elements answering the predicate.
Using sql:variable() allows to introduce a variable's value into the XQuery.

This is - of course! - limited.

The performance will be low.
Using * will include any column, even huge BLOBs.
Your approach creating a SQL-statement dynamically is probably better, but this depends on your needs...

too complicated, and I am searching big data, so performance is important — Med Amin, Jan 29 '21 at 11:48
@MedAmin, Well, in this case you can dynamically add a column alias with something like `CONCAT(' AS ',QUOTENAME(CONCAT(tablename,'.',columnname)))`. — Shnugo, Jan 29 '21 at 14:59
plz can you help me with an example, because I din't found the right query — Med Amin, Jan 29 '21 at 17:02

Query to search in all columns in table with joining

2 Answers2