Fetch Column Name from Table

Question

I have 2 Requirements:

I want to write a SQL Command to fetch the Column Names from a Participant Table where the Column Name must be matching the word %Track%.
I want to fetch all the Table Name in the Database where it contains Column Name LIKE %Track%

"USER_TAB_COLUMNS" Does not run but gives error. Invalid object name 'USER_TAB_COLUMNS'.

SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'V' AND [Name] = 'Participant')

Runs but there is no record fetched by this query.

Please suggest me the Query which will give me the desired resultant.

possible duplicate of [sql select with column name like](http://stackoverflow.com/questions/5274594/sql-select-with-column-name-like) — Tanner, Sep 10 '15 at 09:21
@Tanner I have 2 questions here. This will be serving only one of the requirement. Please unmark it as duplicate if possible — , Sep 10 '15 at 09:41
ok, well this answers the other part of your question then: http://stackoverflow.com/questions/18211645/find-sql-table-name-with-a-particular-column, you should try searching before posting, these types of question are quite common. — Tanner, Sep 10 '15 at 09:44

Rahul Tripathi · Accepted Answer · 2015-09-10T09:26:30.707

2

I want to fetch all the Table Name in the Database where it contains Column Name LIKE %Track%

You can try this

SELECT c.name AS ColumnName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE  '%Track%'

I want to write a SQL Command to fetch the Column Names from a Participant Table where the Column Name must be matching the word %Track%

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Participant' and column_name like '%Track%'

And if you want to find in all the tables the

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name like '%Track%'

edited Sep 10 '15 at 09:26

answered Sep 10 '15 at 09:20

Rahul Tripathi

Both of them actually served my purpose. But could you please help me to improve the query like. the First Query is fetching all the table name more than once. I want the table name to be coming only once. – Sep 10 '15 at 09:40
I am trying to apply `group by` but it is not giving the correct result. – Sep 10 '15 at 09:43
None of the issues raised in the article apply here, so it is not a criticism by any means, more of a side note, but Aaron Bertrand has written a pretty compelling case [against the use of the information schema](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views) – GarethD Sep 10 '15 at 09:52

Jayanti Lal · Answer 2 · 2015-09-10T09:35:29.177

0

try this

   SELECT  *
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE   
                    COLUMN_NAME = 'Your column name'

edited Sep 10 '15 at 09:35

answered Sep 10 '15 at 09:19

Jayanti Lal

score 0 · Answer 3 · edited Sep 10 '15 at 09:32

0

select * from sys.columns 
where name like '%your wild card%' 
and object_id = object_id('tablename')

edited Sep 10 '15 at 09:32

Rahul Tripathi

answered Sep 10 '15 at 09:27

Rewat Sharma

this serve my 1st Requirement. Please also let me know about the second one – Sep 10 '15 at 09:37

score -1 · Answer 4 · edited Sep 22 '15 at 06:35

-1

Try to use INFORMATION_SCHEMA.COLUMNS. It might help you. The query would be like:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Participant'

edited Sep 22 '15 at 06:35

answered Sep 10 '15 at 09:19

Sanket Tarun Shah

4 Answers4