How can I use two information tables in SQL

Question

I need to get a couple of values from the tables in my SQL Server. I have the following SQL statement but its keeps giving an error how can I fix this?

select TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
from INFORMATION_SCHEMA.COLUMNS,
     INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Answer 1

You have to use alias names because both tables contains column names(table name and column name).

SELECT A.TABLE_NAME,
       A.COLUMN_NAME,
       CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.COLUMNS A,
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE B