StealthRT StealthRT - 7 months ago 9
Vb.net Question

SQL Server : getting certain table names from query

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?

Answer

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.