0

How do I check if a column exists in a table and add one if it doesn't exist?(I am using mysql)

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
PRINT 'Your Column Exists'
END

This is a sample one.The results I got one from web are the older versions of mysql. I need the answer in the latest version (mysql server 2019 and above) .How do I solve it?

loksan
  • 157
  • 3
  • 17
  • 1
    You have mysql in your tag, but both mysql and sql-server-2019 in your Question. This is very confusing. Also, if you're looking to find a SQL, why is node.js added as a tag? – Scratte May 27 '20 at 06:40

2 Answers2

1

You can access this kind of information from the information_schema database. It contains a columns table.

The information_schema database is part of ISO SQL, and implemented on all SQL servers :

Here is a portable query :

SELECT count(*) FROM information_schema.columns
WHERE table_schema = 'thedatabase'
  AND table_name = 'thetable'
  AND column_name = 'thecolumn';
LeGEC
  • 46,477
  • 5
  • 57
  • 104
  • I misread the docs, and thought the implementation was different between servers. Which would be awkward : the information_schema database is part of ISO SQL, and should be the same on all implementations. I fixed my answer. – LeGEC May 27 '20 at 07:02
  • SELECT * FROM information_schema.columns WHERE database_name = 'userInformation' AND table_name = 'Articles' AND column_name = 'article3'; //this is showing error 1054 – loksan May 27 '20 at 07:06
  • I got the columns wrong in my initial answer : try `table_schema` instead of `database_name` – LeGEC May 27 '20 at 10:47
0

You can use below query

SHOW COLUMNS FROM table_name LIKE '%column_name%'
nikc.org
  • 16,462
  • 6
  • 50
  • 83
sarwadnya deshpande
  • 111
  • 2
  • 11