SQL Server : getting certain table names from query

Question

Is it possible to display just certain table names in the query:

USE [WebContact] 

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'memberEmails'

Most of the time I would need all the table names but there are curtain situations where I need just certain row names.

When I try doing the following:

USE [WebContact] 

SELECT COLUMN_NAME, ContactDateTime 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'memberEmails'

It tell me that

Invalid column name 'ContactDateTime'

even though that is one of the row names.

Is this possible to do?

`ContactDateTime` *might* be a column **in YOUR table** `memberEmails` - but it's most certainly **NOT** a column in `INFORMATION_SCHEMA.COLUMNS`.... — marc_s, Apr 22 '16 at 16:00

score 2 · Answer 1 · answered Apr 22 '16 at 15:58

2

if ContactDateTime is a column that you are looking for in table memberEmails then you can do this

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'memberEmails'
and COLUMN_NAME='ContactDateTime'

answered Apr 22 '16 at 15:58

cableload

4,215
5
36
62

Does this work with more than one row name? When I try to add another **and** it comes back with blank data. – StealthRT Apr 22 '16 at 16:12
1

in that case, you have to use the in syntax such as `select .... where...column_name in ('ContactDateTime','AnotherColumnName')` – cableload Apr 22 '16 at 16:14

score 2 · Accepted Answer · edited Jul 29 '20 at 18:43

2

The column ContactDateTime may be a column in your table but it is not a column in the INFORMATION_SCHEMA.COLUMNS view.

Since it is not a column there, SQL Server is going to error out saying that it is invalid.

I think what you're trying to do is add another WHERE clause to your statement:

USE [WebContact] 
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'memberEmails'
AND [COLUMN_NAME] = 'ContactDateTime'; -- Here!

Or if you want to add multiple columns...

USE [WebContact] 
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'memberEmails'
AND [COLUMN_NAME] 
  IN ('ContactDateTime', 'column2', 'column3', ... 'column(n)'); -- Here!

Also see here for the case against using INFORMATION_SCHEMAS.

edited Jul 29 '20 at 18:43

Community

1
1

answered Apr 22 '16 at 15:59

Shaneis

1,065
1
11
20

That last example seems to be the one I needed. Thanks Shaneis! – StealthRT Apr 22 '16 at 16:13
1

ah...yeah...no problem...seriously though learn the joys that is `sys.columns` and `sys.tables` and leave `INFORMATION_SCHEMA` to itself :) – Shaneis Apr 22 '16 at 16:16

score 0 · Answer 3 · answered Apr 22 '16 at 16:00

0

The view INFORMATION_SCHEMA.COLUMNS don't have column name ContactDateTime. Please read document first

answered Apr 22 '16 at 16:00

Pham X. Bach

5,284
4
28
42

SQL Server : getting certain table names from query

3 Answers3