One question came up just a day ago while I was writing SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN.
How many tables in database AdventureWorks have column name like ‘EmployeeID’?
It was quite an interesting question and I thought if there are scripts which can do this would be great. I quickly wrote down following script which will go return all the tables containing specific column along with their schema name.
USE AdventureWorks
GO
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 '%EmployeeID%'
ORDER BY schema_name, table_name;
In above query replace EmployeeID with any other column name.
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
ORDER BY schema_name, table_name;
If you want to find all the column name from your database run following script. You can down any condition in WHERE clause to get desired result.
Reference : Pinal Dave (https://blog.sqlauthority.com)
191 Comments. Leave new
This is beautiful, thank you so much. Works perfectly!
Also, when searching for ethnic this is the query I used:
USE [INSERT DATABASE NAME]
GO
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 ‘%ethnic%’
ORDER BY schema_name, table_name;
Hi, I would like to know how search for a column name with instances in several tables within a database and return the column value for each.
Great help but you could solve capital sensitivity with
WHERE UPPER(c.name) LIKE ‘%VOLUME%’
If I just know a Database Name alone and wanted to know all maximum used columnnames across tables, is there any query to find that? this helps in understanding any Database for a first person logging into that DB
i want to find a column in a table when that column is updated by update query
thanks in advance…….
and insert that record in another table
thanks in advance….
I am so annoyed with this error
SELECT `groups`.`name`, `groups`.`title` FROM `v242_config_groups` `groups` JOIN `v242_config` `config` ON `groups`.`name` = `config`.`group_name` GROUP BY `groups`.`name` ORDER BY `groups`.`order` ASC
What is the error you are getting?
Thanks for another very useful query PD! Appreciate all the work you do sharing your knowledge.
Great post and responses. I’ve learned a lot from reading it.
awsome
You can simple use select * from all_Tab_Columns where COLUMN_NAME like ‘%XYZ%’
Please Ans Me Question Serch NAme Plus Quantity
Can you please explain more Hassan?
Simple solution:
Select * From INFORMATION_SCHEMA.COLUMNS
Where COLUMN_NAME =’Enter Column Name Here’
Simple solution:
select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME =’Enter Column Name Here’
Correct reflexion213.
Very useful, thanks a lot.
You can also RUN this one line Query To get the Result on your SCHEMA…
select Table_name from USER_TAB_COLUMNS where column_name LIKE ‘%EMP_ID%’;
Hello,
Can anyone help me with the following.
I want to find column value = ‘%Trans%’ in all the tables in Test database.
Is there any easiest way to find that? There are lots of scripts and stored procedures available on the internet but I am looking for some simplest way.
Any help would be appreciated.
Thanks.
Very Helpful
Thanks.
Super, thanks
how can i check all tables of a particular database for identifying a unique(name.id) combination and determine the name of this table so that later on i can update the value of name to another value.
can you provide an example Swetha?