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
– marc_s, Commented Apr 22, 2016 at 16:00

Community · Accepted Answer · 2020-07-29 18:43:14Z

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, 2020 at 18:43

CommunityBot

11 silver badge

answered Apr 22, 2016 at 15:59

Shaneis

1,0951 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StealthRT Over a year ago

That last example seems to be the one I needed. Thanks Shaneis!

Shaneis Over a year ago

ah...yeah...no problem...seriously though learn the joys that is sys.columns and sys.tables and leave INFORMATION_SCHEMA to itself :)

cableload · Accepted Answer · 2016-04-22 15:58:41Z

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, 2016 at 15:58

cableload

4,4156 gold badges42 silver badges65 bronze badges

2 Comments

StealthRT Over a year ago

Does this work with more than one row name? When I try to add another and it comes back with blank data.

cableload Over a year ago

in that case, you have to use the in syntax such as select .... where...column_name in ('ContactDateTime','AnotherColumnName')

Pham X. Bach · Accepted Answer · 2016-04-22 16:00:28Z

0

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

answered Apr 22, 2016 at 16:00

Pham X. Bach

5,4724 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

SQL Server : getting certain table names from query

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related