How to Return the Column Names of a Table

How do you return the column names of a table?

Not sure if there is an easier way in 2008 version.

USE [Database Name]
SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName'

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 can I get column names from a table in SQL?

MYSQL, MS SQL and Postgresql (thanks @christophe)

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'database_name'
AND TABLE_NAME = 'table_name'

PIVOT the results if you need column names in one line

Function returning a table for given table and column names

SQL is a strictly typed language and Postgres functions must declare their return type. Returning a variable number of columns from a function is only possible with workarounds, like polymorphic types. See:

• How to return a table by rowtype in PL/pgSQL

But we can't work with the row type in your case, as that varies from database to database. The remaining option: return anonymous records and provide a column definition list with every call. I don't usually recommend this, as providing a column definition list with every call can be tedious - and often pointless. But yours might be one of the rare use cases where it makes sense.

Still, you have to know the data type of possibly missing columns. I'll assume integer for the purpose of this demo. Else you have to pass data types additionally and build the query accordingly.

CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl regclass
                                          , _cols VARIADIC text[])  -- ①
  RETURNS SETOF record     -- ② anonymous records
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE    -- ③ dynamic SQL
   format(
      'SELECT %s FROM %s'  -- ④ safe against SQLi
    , (
      SELECT string_agg(COALESCE(quote_ident(a.attname)
                              , '0 AS ' || quote_ident(t.col)  -- assuming integer!
                                ), ', ' ORDER  BY t.ord) -- ⑤
      FROM   unnest(_cols) WITH ORDINALITY t(col, ord)   -- ⑤
      LEFT   JOIN pg_attribute a ON a.attrelid = _tbl    -- ⑥
                                AND a.attnum > 0
                                AND NOT a.attisdropped 
                                AND a.attname = t.col
      )
    , _tbl
   );
END
$func$;

Call (important!)

SELECT *
FROM   f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int); -- ② column definition list

Your example call, with expressions based on these columns:

SELECT pool, case when spa = 1 then 1 else 0 end as has_spa  -- ⑦ expressions
     , sauna, house_size
     , case when no_rooms > 2 then 1 else 0 end as rooms 
FROM f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);

db<>fiddle here

① The function takes a table name as regclass type. See:

• Table name as a PostgreSQL function parameter

... followed by an arbitrary list of column names - in meaningful order. VARIADIC should be convenient for this. See:

• Pass multiple values in single parameter

Note that we pass column names as case-sensitive, single-quoted strings. Not (double-quoted) identifiers.

② This may be the first time ever I recommend returning anonymous records from a function - after close to 1000 answers on the [plpgsql] tag. The manual:

If the function has been defined as returning the record data type,
then an alias or the key word AS must be present, followed by a column
definition list in the form ( column_name data_type [, ... ]). The
column definition list must match the actual number and types of
columns returned by the function.

③ The manual on dynamic SQL.

④ Safe against SQL injection, because the table name is passed as regclass, and the SELECT list is concatenated using quote_ident() carefully. See:

• Define table and column names as arguments in a plpgsql function?

⑤ Use WITH ORDINALITY to preserve original order of columns. See:

• PostgreSQL unnest() with element number

⑥ LEFT JOIN to the system catalog pg_attribute to identify existing columns. See:

• Select columns with particular column names in PostgreSQL

⑦ Move expressions building on the passed columns to the outer SELECT.

---

Disclaimer: I would only introduce this level of sophistication if I had to. Maybe you can work with simple views in each database after all?

return the column name from a table where a specific value in any row were found with python and pandas

IIUC, use:

df.columns[df.eq(100).any()]

#Index(['A', 'B'], dtype='object')

To Get output as series, call pd.Series() : pd.Series(df.columns[df.eq(100).any()])

How to return all column names/titles (and avoid TypeError: table.columns(...).names is not a function )?

Here is a possible solution:

table.columns().header().toArray().map(x => x.innerText)

I used the API docs from DataTable. Replacing innerText with innerHTML also works.

How to return column names from SQL database

If you run the following SQL you'll get an empty rowset. From which you can interpret the column names by using a SqlCommand and DataReader.

using (var conn = new SqlConnection("your_conn_string"))
{
    var command = new SqlCommand("select * from [dbo].[tableName] where 1 = 2");
    conn.Open();

    using(var dr = command.ExecuteReader())
    {
        var columns = new List();

        for(int i=0;i        {
           columns.Add(reader.GetName(i));
        }
    }
}

SQL: Return Column names where column contains a given Value

-- input parameters (guessing on type for @value):

DECLARE 
  @schema SYSNAME = N'dbo', 
  @table  SYSNAME = N'z', 
  @value  VARCHAR(64) = '75';


-- now, inside the procedure body:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''cols:'' + STUFF(''''';

SELECT @sql += N' 
  + CASE WHEN EXISTS (SELECT 1 FROM ' 
  + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
  + ' WHERE TRY_CONVERT(VARCHAR(64), ' + QUOTENAME(c.name) 
  + ') = @value) THEN '', ' + c.name + ''' ELSE '''' END'
FROM sys.tables AS t
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = @table AND s.name = @schema;

SET @sql += N', 1, 1, '''');'

PRINT @sql;

--EXEC sp_executesql @sql, N'@value VARCHAR(64)', @value;

When you are happy with the output, uncomment the EXEC.

So let's consider a simple table:

CREATE TABLE dbo.floob
(
  a INT, 
  b VARCHAR(32), 
  c VARBINARY(22), 
  d DATE, 
  e DATETIME, 
  f ROWVERSION
);

INSERT dbo.floob(a,b,c,d,e) VALUES
( 75, 'foo', 0x00, GETDATE(), GETDATE()),
( 21, '75',  0x00, GETDATE(), GETDATE());

Now, a stored procedure based on the above code:

CREATE PROCEDURE dbo.FindStringInAnyColumn
  @schema SYSNAME = N'dbo', 
  @table  SYSNAME,
  @value  VARCHAR(64)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT ''cols:'' + STUFF(''''';

  SELECT @sql += N' 
    + CASE WHEN EXISTS (SELECT 1 FROM ' 
    + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
    + ' WHERE TRY_CONVERT(VARCHAR(64), ' + QUOTENAME(c.name) 
    + ') = @value) THEN '', ' + c.name + ''' ELSE '''' END'
  FROM sys.tables AS t
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = @table AND s.name = @schema;

  SET @sql += N', 1, 1, '''');'

  EXEC sp_executesql @sql, N'@value VARCHAR(64)', @value;
END
GO

Sample usage:

EXEC dbo.FindStringInAnyColumn @table = N'floob', @value = '75';

Output:

Cols: a, b

MySQL query return column names in comma separated list where value is null

Use CASE expressions to check for nulls and CONCAT_WS to concatenate the results:

SELECT id,
       CONCAT_WS(
         ',',
         CASE WHEN name IS NULL THEN 'name' END,
         CASE WHEN age IS NULL THEN 'age' END,
         CASE WHEN location IS NULL THEN 'location' END
       ) missing  
FROM applicants
WHERE name IS NULL OR age IS NULL OR location IS NULL 
ORDER BY id;

See the demo.

---

Related Topics