To Get Column Names from Table Having a Particular Value

To get column names from table having a particular value

-- Test table
declare @T table(ID int, Col1 varchar(10), Col2 int, Col3 bit, Col4 varchar(max))
insert into @T values (1, '123', 123, 1, 'Some text long text')

-- ID to get one row
declare @ID int
set @ID = 1

-- Value to search for
declare @Val varchar(10) 
set @Val = '123'

select 
  Col.value('local-name(.)', 'varchar(max)') as ColName
from (select *
      from @T
      where ID = @ID
      for xml path(''), type) as T(XMLCol)
  cross apply 
    T.XMLCol.nodes('*') as n(Col) 
where Col.value('.', 'varchar(10)') = @Val  

Result

ColName
--------
Col1
Col2

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

How to retrieve a column name from a table that is stored as a value in another table

i add the [Last Refreshed] column to my tables and write this query and give me the correct answer

DROP TABLE  IF EXISTS #DB_DUMMY

CREATE TABLE  #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

DECLARE @COMMAND NVARCHAR(MAX)

SELECT @COMMAND = STRING_AGG(' INSERT INTO #DB_DUMMY SELECT DISTINCT '+CHAR(39)+T.name+CHAR(39)+',['+C.name+'] FROM '+S.name+'.'+T.name + ' GO', CHAR(13)+CHAR(10))
FROM sys.all_columns C
INNER JOIN sys.tables T ON C.object_id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = 'Last Refreshed'

PRINT(@COMMAND)

EXEC(@COMMAND)

SELECT * FROM #DB_DUMMY

two first line with IF EXISTS is new syntax in sql server 2017

How to get column name of particular value in sql server 2008

I think you want a list of column names that match a particular value.

To do that you can create a XML column in a cross apply for each row and the use nodes() in a second cross apply to shred on the elements that has the value you are looking for.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table dbo.CurrentReport
(
  ID int primary key,
  Col1 varchar(10),
  Col2 varchar(10),
  Col3 varchar(10)
);

go

insert into dbo.CurrentReport(ID, Col1, Col2, Col3) values(1, 'Value1', 'Value2', 'Value3');
insert into dbo.CurrentReport(ID, Col1, Col2, Col3) values(2, 'Value2', 'Value2', 'Value2');
insert into dbo.CurrentReport(ID, Col1, Col2, Col3) values(3, 'Value3', 'Value3', 'Value3');

Query 1:

-- Value to look for
declare @Value varchar(10) = 'Value2';

select C.ID, 
       -- Get element name from XML
       V.X.value('local-name(.)', 'sysname') as ColumnName
from dbo.CurrentReport as C
  cross apply (
              -- Build XML for each row
              select C.* 
              for xml path(''), type
              ) as X(X)
  -- Get the nodes where Value = @Value
  cross apply X.X.nodes('*[text() = sql:variable("@Value")]') as V(X);

Results:

| ID | ColumnName |
|----|------------|
|  1 |       Col2 |
|  2 |       Col1 |
|  2 |       Col2 |
|  2 |       Col3 |

How to get column name from the table with particular value

You can use array_keys() with the second parameter:

$object = DB::table('weak')->first();
$columns = array_keys((array)$object, 1);

The result will be:

['Mon', 'Tue', 'Wed']

---

Related Topics