0

I did google this a bit but my biggest problem is not knowing what to search. I felt like no matter what I searched it wasn't my problem so if you have a link to another question do to my search inability feel free to link it and be done with it.

Say I have like 250 different fields (columns) and they are all from 1 of 10 tables but I need to figure out which field comes from which table. Maybe i could do a massive select statement but there are possible duplicate column names and that sound like a headache

looking at each column name and the scouring through all these tables to find each one seems too difficult? Any thoughts? Does my question make sense? Let me know if you need more information.

Sagick
  • 317
  • 2
  • 6
  • 20

2 Answers2

2

you can use information_schema.columns to get information

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE 
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME in ('col1', 'col2', ...)
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • whenever I do this I get quite a few invalid column names. does that mean i have have an incorrect list of column names? – Sagick Oct 08 '20 at 19:46
2

For SQL Server you can find metadata information from the system base tables

   SELECT t.name AS table_name,
           c.name AS column_name
      FROM sys.tables AS t
      JOIN sys.columns AS c ON t.object_id = c.object_id
     WHERE c.name IN('col1','col2') --give the columns names here.

Now, this code is specific to SQL Server and not a portable code. But few cases to be noted that instead of using INFORMATION_SCHEMA which doesn't always have all the information like if the column has an IDENTITY property as it is proprietary to SQL Server.

You can read more about this here

Biju jose
  • 263
  • 2
  • 15