Get column names in SQL server that satisfy a where condition on data

Question

I just had a random doubt while working with SQL-server to which i thought i could get it clarified here. Say- i have a condition that i want to find out all the column names in the database which satisfy my where condition on data.

Example:- There are some 20-30 tables in a SQL-Server DB.Now all i need is a query to find out the list of column names which have "Ritesh" as a data field in them. I don't know if it is really possible in the first place.

I hope i am clear. Please, any help will be most appreciated.

Thank You. Ritesh.

Did you try using search? http://stackoverflow.com/questions/63953ing/mysql-search-in-all-fields-from-every-table-from-a-database — Andrew, Jun 11 '14 at 05:35
information_schema.columns is your friend. http://msdn.microsoft.com/en-us/library/ms186778.aspx — John Powell, Jun 11 '14 at 05:37
@Andrew, that page has been removed, though to be fair, searching does yield hundreds of results. — John Powell, Jun 11 '14 at 05:38
Is this a real requirement? There is no easy way to do this. If I am understanding correctly, you want to get the column names of columns, containing a specific value? — Raj, Jun 11 '14 at 05:51
@JohnBarça i have no idea why direct link isn't working, try http://stackoverflow.com/q/639531/560972 it's very popular SO question/answer page — Andrew, Jun 11 '14 at 07:31
@Andrew, that's better. Seems information_schema is the way to go. — John Powell, Jun 11 '14 at 07:43
Whenever i need to do such search, i always go 'phpMyAdmin way', very conventient and fast — Andrew, Jun 11 '14 at 07:56
Yea @Raj, This is a real requirement in a project am working on. This is something like i have a search box and when i search for some word in it, I must know which column i got the result from. — Ritesh, Jun 11 '14 at 10:09

score 2 · Accepted Answer · answered Jun 11 '14 at 06:16

This should work, but be aware, this will take a while to execute in large databases. I have assumed that the search string might be just a part of the data contained and am using wildcards. I feel this is purely academic, as I am unable to imagine a scenario, where this will be required.

--you need to iterate the whole columns of entire table to find the matched record
--another thing is that you need dynamic sql to find the table name

DECLARE @Value varchar(50) --value for that find the column Name
SET @Value = 'Ritesh'

CREATE TABLE #Table
(
    TableName Varchar(500),ColumnName Varchar(500),
    Id int Identity(1,1) --use for iteration
)
CREATE TABLE #Results
(
TableName varchar(500),
ColumnName varchar(500)
)
INSERT INTO #Table
    SELECT
        TABLE_SCHEMA + '.' + TABLE_NAME AS TableNam,
        Column_name AS ColumnName
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Data_type IN ('char', 'nchar', 'varchar', 'nvarchar')
--change the datatype based on the datatype of sample data you provide
-- also remember to change the wildcard, if the input datatype is not a string

DECLARE @Count Int --total record to iterated
SET @Count = 0;


SELECT
    @Count = COUNT(*)
FROM #Table


DECLARE @I int --initial value one to iterate
SET @I = 1;


DECLARE @TableName varchar(500)
SET @TableName = ''
DECLARE @ColumnName varchar(500)
SET @ColumnName = ''


DECLARE @Str nvarchar(1000)
SET @Str = ''
DECLARE @param nvarchar(1000)
SET @param = ''


DECLARE @TableNameFound varchar(max)
SET @TableNameFound = ''


DECLARE @Found bit
SET @Found = 0;

WHILE @I<=@Count
BEGIN

SET @Found = 0;
SELECT
    @TableName = TableName,
    @ColumnName = ColumnName
FROM #Table
WHERE Id = @I;
SET @param = '@TableName varchar(500),@ColumnName varchar(500),@Value varchar(50),@TableNameFound varchar(max),@Found bit output'
SET @str = 'Select @Found=1 From ' + @TableName + ' where ' + @ColumnName + ' Like ' + '''' + '%' + @Value + '%' + ''''

-- here we are using tablename and actual value to find in table
EXEC sp_executesql  @str,
                    @param,
                    @TableName,
                    @ColumnName,
                    @Value,
                    @TableNameFound,
                    @Found OUTPUT

    IF @Found=1
    BEGIN
    INSERT INTO #Results (TableName, ColumnName)
    SELECT
        @TableName,
        @ColumnName
    END

--increment value of @I
SET @I = @I + 1;
END

--Display Results
SELECT * FROM #Results

--Clean Up
DROP TABLE #Table
DROP TABLE #Results

Thanks for the reply Raj.I will definitely try this and get back to you. — Ritesh, Jun 11 '14 at 10:18

Get column names in SQL server that satisfy a where condition on data

1 Answers1