CODEWITHSUNDEEP
sqlsql-servert-sqlschemadatabase-schema
Ramapriyan C
Ramapriyan C

Reputation: 67

Display specific columns in Sql Tables

How can I display only the specfic columns from INFORMATION_SCHEMA.COLUMNS schema?

Ex:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'rate_Structure'
ORDER BY ORDINAL_POSITION

Returns:

+-------------+
|             |
| COLUMN_NAME |
+-------------+
|    a        |
|    b        |
|    c        |
|    d        |
|    e        |
+-------------+

How to show only a and e columns?

Upvotes: 2

Views: 7664

Answers (4)

Ilyes
Ilyes

Reputation: 14928

how to Show only a and e columns

Simply, by filtering the results in WHERE clause as

SELECT COLUMN_NAME    
FROM INFORMATION_SCHEMA.COLUMNS    
WHERE TABLE_NAME = 'rate_Structure' and COLUMN_NAME in ('a','e') 
-- You can also use: and (COLUMN_NAME = 'a' or COLUMN_NAME = 'e')    
ORDER BY ORDINAL_POSITION

You need to add the condition to get what you need, and COLUMN_NAME in ('a','e') will return only COLUMN_NAME has 'a' or 'e' value.

Upvotes: 2

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can use INFORMATION_SCHEMA.COLUMNS as below

USE Test
GO
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SalesTerritory'
AND COLUMN_NAME = 'Region'

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

Add another condition in where 

SELECT COLUMN_NAME    
FROM INFORMATION_SCHEMA.COLUMNS    
WHERE TABLE_NAME = 'rate_Structure' and COLUMN_NAME in ('a','c')    
ORDER BY ORDINAL_POSITION

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

You need to add aditional conditions like:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'rate_Structure'  
  AND data_type = 'int'    -- condition to get only specific columns
ORDER BY ORDINAL_POSITION;

Upvotes: 1

Related Questions