Getting specific column name of a table

Question

I am working on a project and I got stuck with a query in which I want to get the specific column name of a table whose value is "no."

I have a table with name subscription and it has four fields; id(integer), email(varchar), sms(varchar), phone(varchar) and my email, sms and phone field have values either 'yes' or 'no'.

Now I want to retrieve the name of only those columns which has a value 'no'. I want to execute this query in mysql.

I have tried the following query which is returning me the complete row wherever there is even one occurence of 'no':

SELECT * FROM test.subscription WHERE 'no' IN (email,sms,phone);

Like i have these two roes now i want to select the column name where value='no' and id=1

Like i have these two rows now i want to select the column name where value='no' and id=1

You will probably have to query the system tables to get table meta data such as column names, this post should help - http://stackoverflow.com/questions/4849652/sql-server-2008-find-all-tables-containing-column-with-specified-name — Neil Hibbert, May 21 '13 at 07:24
we want to choose only that column name where the value is 'no' not where the column name starts with some specific character — rupinder18, May 21 '13 at 07:31
But can't you do this client side? The query you supply can form the basis and then you can filter the columns with the value 'no'. — Erik Schierboom, May 21 '13 at 07:36
If you have a row with the values id:`1`; email:`Yes`; sms:`No`; phone:`Yes` you want to return a string or a string array containing `"sms"`? — Hjalmar Z, May 21 '13 at 07:36

score 2 · Answer 1 · answered May 21 '13 at 08:25

How about something cheap and cheerful using a case statement like:

SELECT ID, 'ColName' = CASE
    WHEN email = 'no' THEN 'email'
    END, 
    'ColName2' = CASE
    WHEN sms = 'no' THEN 'sms'
    END, 
    'ColName3' = CASE
    WHEN phone = 'no' THEN 'phone'
    END
FROM subscription
where ID = 2

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

I cant put this as a comment because of less privileges, but if you are looking to search a value which is held by a table (could be any column) - you might check this SO post

Using the code from the same post -

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

DECLARE @SearchStr nvarchar(100)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = 'no'
SET @SearchStr2 = QUOTENAME('%' + 'no' + '%','''')

    SET @ColumnName = ''
    SET @TableName = '[test].[dbo].[subscription]'

    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 


SELECT ColumnName, ColumnValue FROM @Results

edited May 23 '17 at 12:13

Community

1
1

answered May 21 '13 at 07:38

siddharth

660
2
6
18

I have tried this code but it gives the following error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) DE' at line 1 – rupinder18 May 21 '13 at 07:55
I edited to search the string 'no' in the above code. I have tested it and it runs okay. Can you make sure you are running this separately in a new query window and nothing above it which might cause it to error at this line may be. – siddharth May 21 '13 at 08:05
I tried to execute agian but getting the same error again...have you executed this query in mysql query browser – rupinder18 May 21 '13 at 08:50
I am using SQL Server and you are using MYSQL. So the problem is with the syntax I think. You should use the 'CREATE TEMPORARY TABLE Results' I think. But I am not sure if the rest will work with MYSQL as well. – siddharth May 21 '13 at 08:59
can you paste the result what you are getting – rupinder18 May 21 '13 at 09:16
I am sorry, I did not pay attention while reading the question that it should be MYSQL. I work in a SQL Server environment and hence the issue. I would advice you to ignore the above answer and I am sorry if this has wasted any time and effort. Please check alternative methods in MYSQL - the following link may help - http://stackoverflow.com/questions/639531/mysql-search-in-all-fields-from-every-table-from-a-database/ – siddharth May 21 '13 at 09:28
its okk but just paste the result which you got after executing the above code in sql server – rupinder18 May 21 '13 at 09:31
I get back data in the form of columnName and columnValue - [myDBName].[dbo].[myTable].[myColName] %searchString% – siddharth May 21 '13 at 09:56

score 0 · Answer 3 · answered May 21 '13 at 08:12

0

If i got the Question right, i think the following Query might work

SELECT * 
FROM test.subscription 
WHERE 1=1
and 
(email = 'no'
or
email = 'no'
or 
sms = 'no'
)
and id=(your values)

answered May 21 '13 at 08:12

Prahalad Gaggar

11,389
16
53
71

this query results the same result as my query but i dont want this please read again and help me – rupinder18 May 21 '13 at 08:49

Getting specific column name of a table

3 Answers3