CODEWITHSUNDEEP
sqlsql-serversql-server-2008
S.p
S.p

Reputation: 1059

Finding the specific column (field) name from multiple tables in a database

I have large numbers of tables in my database. I am searching for a column named Country, but don't know which table contains that column. Is there a specific query that will help me to find the name of the table containing this column?

Upvotes: 4

Views: 23925

Answers (5)

Suresh Bkonda
Suresh Bkonda

Reputation: 11

If we want to find a column name with like operator in more than one tables (ex: 3 tables) then go for below query:

select COLUMN_NAME, TABLE_NAME 
from ALL_TAB_COLUMNS 
where TABLE_NAME in ('SIL_RLS_UPLOAD_TMP','SIL_CUSTOMER_MST','SIL_PERSONAL_CUSTOMER_MST')
      and upper(column_name) like upper('%loan%');

If we want to find a column name with like operator in all tables then go for below query:

select * 
from all_tab_columns 
where upper(column_name) like upper('%card%');

Upvotes: 0

thavan
thavan

Reputation: 2429

select distinct table_schema, table_name from information_schema.columns where table_name = 'Country';

Upvotes: 2

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ColumnName%'
ORDER BY schema_name, table_name;

Replace ColumnName to your actual column name

Upvotes: 4

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can find such of information in the INFORMATION_SCHEMA.COLUMNS

USE YourDBName;
SELECT DISTINCT Table_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_Name = 'Country';

Upvotes: 1

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

Yes, you can use INFORMATION_SCHEMA.COLUMNS

SELECT DISTINCT 
   TABLE_NAME 
FROM 
   INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Country'

Upvotes: 9

Related Questions