locked
Get the column names of a table. RRS feed

  • Question

  • Hello All Experts,

    I want to get the column names of the table such that the  value of the column after inserting a record will be 1000000.

    can anyone help me in getting this resolved.

    Friday, June 2, 2017 6:19 AM

Answers

  • Hi EmpAnsar,

    By using above method, it will return all the column names which its value equals to 10000000. No matter how many rows in the table. For example:

    CREATE TABLE test
      (
         A INT,
         B INT,
         C INT,
         D INT
      )
    
    INSERT INTO test
    VALUES      ( 10000000,0,10000000,1 ),
    ( 10000000,0,10000000,10000000 ),
    ( 10000000,10000000,10000000,1 ),
    ( 10000000,0,10000000,1 ),
    ( 10000000,0,10000000,1 ),
    ( 10000000,0,10000000,10000000 ),
    ( 10000000,0,10000000,1 )

    And it will return:

    CName:
    A
    C
    A
    C
    D
    A
    B
    C
    A
    C
    A
    C
    A
    C
    D
    A
    C

    These are all the column names of 10000000 valued columns.

    If it doesn't fit your requirement, then could you please share us some sample data or your expected result if possible?

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by EmpAnsar Tuesday, June 6, 2017 8:56 AM
    Monday, June 5, 2017 6:31 AM

All replies

  • Hello,

    Sorry, but for it is not clear what you are looking for; can you explain it more detailed and give us an example, please?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 2, 2017 6:54 AM
  • Hi EmpAnsar,

    If you want to get the column names of the table. You can use sys.column table. Please refer:

    SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName') 
    
    --Or
    
    SELECT o.Name, c.Name
    FROM sys.columns c 
    JOIN sys.objects o ON o.object_id = c.object_id 
    WHERE o.type = 'U' 
    ORDER BY o.Name, c.Name
    

    If you still have any question, please feel free to ask.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 2, 2017 7:17 AM
  • i HAVE FOUR RECORDS IN A TABLE.

    i want to check 

    if colvalue==1000000

    select all the columns names  satisifying above condtion.

    my table contains 150 columns.

    hope you guys understand the query?

    Friday, June 2, 2017 8:38 AM
  • Hi EmpAnsar,

    Please try this:

    CREATE TABLE test
      (
         A INT,
         B INT,
         C INT,
         D INT
      )
    
    INSERT INTO test
    VALUES      ( 10000000,0,10000000,1 )
    
    DECLARE @collist VARCHAR(max)='',
            @sql     NVARCHAR(max)
    
    SELECT @collist += '(''' + COLUMN_NAME + ''',' + COLUMN_NAME + '),'
    FROM   INFORMATION_SCHEMA.columns
    WHERE  TABLE_NAME = 'test'
    AND TABLE_SCHEMA = 'dbo'
    
    SELECT @collist = LEFT(@collist, Len(@collist) - 1)
    
    SET @sql ='
    SELECT Cname
    FROM   test
           CROSS apply (VALUES' + @collist
              + ') ca (cname, data)
    WHERE  data = 10000000 '
    
    exec Sp_executesql
      @sql
    
    --drop table test
    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 2, 2017 8:57 AM
  • Hello Xi JIn,

    in dev environment we have four rows but in production there could be any number of rows.

    how to make the above dynamic ?

    Friday, June 2, 2017 9:10 AM
  • Hi EmpAnsar,

    By using above method, it will return all the column names which its value equals to 10000000. No matter how many rows in the table. For example:

    CREATE TABLE test
      (
         A INT,
         B INT,
         C INT,
         D INT
      )
    
    INSERT INTO test
    VALUES      ( 10000000,0,10000000,1 ),
    ( 10000000,0,10000000,10000000 ),
    ( 10000000,10000000,10000000,1 ),
    ( 10000000,0,10000000,1 ),
    ( 10000000,0,10000000,1 ),
    ( 10000000,0,10000000,10000000 ),
    ( 10000000,0,10000000,1 )

    And it will return:

    CName:
    A
    C
    A
    C
    D
    A
    B
    C
    A
    C
    A
    C
    A
    C
    D
    A
    C

    These are all the column names of 10000000 valued columns.

    If it doesn't fit your requirement, then could you please share us some sample data or your expected result if possible?

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by EmpAnsar Tuesday, June 6, 2017 8:56 AM
    Monday, June 5, 2017 6:31 AM
  • Please check whether below script satisfies your requirement:

    create table data (a int, b int, c int, d varchar(100), e int, f int)
    
    insert into data values(1,2,1000000,'string1',54,36)
    insert into data values(1000000,10,28,'string1',34,74)
    insert into data values(54,1000000,43,'string1',45,87)
    insert into data values(1000000,32,34,'string1',34,56)
    insert into data values(42,62,1000000,'string1',74,86)
    
    create table #result(colname varchar(1000))
    
    declare @query varchar(max)
    declare @temp table(query varchar(max))
    insert into @temp select 'insert into #result select case when isnumeric(' + column_name + ') = 1 and ' + column_name + '= 1000000 then column_name end from data,INFORMATION_SCHEMA.columns where table_name = ''data'' and column_name =''' + column_name + '''' from INFORMATION_SCHEMA.columns where table_name = 'data'
    
    while exists(select * from @temp)
    begin
    select top 1 @query = query from @temp
    exec(@query)
    delete top (1) from @temp
    end
    select distinct colname from #result where colname is not null
    Monday, June 5, 2017 10:06 AM