CODEWITHSUNDEEP
mysqlsql
tatty27
tatty27

Reputation: 1554

MySQL count columns in table by name

Is it possible to count the number of columns in a table by their name?

eg

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN LIKE '%name%'
AND table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'

I have a table that adds columns to a table for custom fields added by the user and I need to it count the added columns to use the result in a while loop

Upvotes: 0

Views: 891

Answers (3)

Brownbeard
Brownbeard

Reputation: 67

Something like this could also work: 

SELECT T.name, COUNT(C.name) FROM sys.tables T
INNER JOIN sys.columns C ON C.object_id = T.object_id
WHERE T.name = ('TableName') and C.name like '%name%'
GROUP BY T.name

This would work within a database.

Upvotes: 0

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7678

In your query LIKE

Place LIKE %'name'% as LIKE '%name%'

Upvotes: 0

Jeffrey Blake
Jeffrey Blake

Reputation: 9709

Yes, this is possible. There's a chance that you need to use COLUMN_NAME in the WHERE in place of COLUMN -- at least, that's how it is listed in the MySQL Docs:

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%name%'
   AND table_schema = 'DB_NAME'
   AND table_name = 'TABLE_NAME'

Upvotes: 2

Related Questions