Click here to Skip to main content
14,269,309 members
Rate this:
Please Sign up or sign in to vote.
See more:
In my previous interview , interviewer asked me about sql query

suppose i have student databse & it contain 10 tables

i want to find an rows from tables containing specific word .

Restrictions as below

1. I don't have table name
2. I don't have column name
3. I just have Database name & the word to be searched.

Is this possible to do? if yes can u plz share the query

Thanks for Help......
Posted
Updated 3-Feb-14 17:52pm
v6
Rate this:
Please Sign up or sign in to vote.

Solution 1

To the best of my knowledge, you would first need to query to get the names of the tables and columns.

There isn't a standardized SQL way to do this, but most RDBMS implementations do offer a way to do it. This is called metadata or schema querying. Sometimes the RDBMS provides helpers to do this, sometimes it exposes the schema as a table or view that can be queried. For example:

MySQL


SHOW TABLES;
SHOW COLUMNS IN Customers;
or
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Customers';

T-SQL


SELECT * FROM information_schema.tables; /* SQL Server >= 2005 */
SELECT * FROM sysobjects WHERE xtype='U'; /* SQL Server < 2005; U == User table */
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Customers';
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

I think, He/She is targeting something like:

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

I am not sure why the interviewer ask this kind of question
but as per the question the following query will produce
the result in MssqlServer 2005 and above


USE <databasename>
DECLARE @word AS VARCHAR(100) ='Test'
DECLARE @sql AS NVARCHAR(MAX) =''
SELECT @sql = @sql + ISNULL('SELECT * FROM ['+T.name+'] WHERE  '+STUFF((
        SELECT 'OR  ['+c.[name]+'] like ''%'+@word +'%'' '
        FROM   sys.[columns] AS c
        WHERE  TYPE_NAME(c.system_type_id) IN ('varchar', 'nvarchar', 'char', 'text', 'ntext') AND
               c.[object_id] = t.[object_id]
               FOR XML PATH('')
       ), 1,3,'') +';
       ','')
FROM   sys.tables AS t
EXEC sp_executesql @sql</databasename>
   
v2
Comments
patil.nitin8358 4-Feb-14 1:29am
   
nice job...
but it work for only IN ('varchar', 'nvarchar', 'char', 'text', 'ntext') for int how will it work ?

if i add int inside the IN it gives error
Thava Rajan 4-Feb-14 1:43am
   
cast the column as varchar if it is int

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100