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
I know only DBMS concepts
give me tips to know SQL
Which one is best, * or Specified column names in a Select Query?Why?
SELECT col_list is best. Because it will not break even if new columns are added to the base table
this just saved me from digging through a few thousand lines of
code. Thanks :)
this just saved me from digging through a few thousand lines of source code. Thanks.
Thanks so much! Your blog has been very helpful to me lately! :)
I just wanted to finally say thanks – every time I google a tsql question, whenever your face appears, I follow the link knowing I’ll get good, solid, reliable advice. You sir, give excellent nuggets and are quite an asset to the internet at large. Thank you.
Pinal Sir, the 2nd best part of your blog posts is the large no. of comments, through which one can learn different ideas.
Thanks for providing us such a useful blog !!!
I have a large database and I want to determine how many tables have a specific column with a specific value in it.
This works great and I just tried it with date and the only thing I can think of is data type…such as date datetime or smalldatetime
how would I add a column for the format of the date ?
here is the result
SELECT Table_Schema, Table_Name, Column_Name, Data_Type
FROM information_schema.columns
WHERE table_name in ( select name from sys.objects
where type = ‘U’ )
and column_name like ‘%EmployeeID%’
order by table_schema, table_name
Thanks Johirul that really helped.
Hi,
Can someone help me with a script that looks at how many tables in oracle schema that have column name like ‘EmployeeID’?
Actual I need a very same script that works like the one below but in Oracle aqua, not in ms server.
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;
Thanks in advance
thank you works like a charm
Hi Dave,
I have a question.
Is it possible for you to get data for particulat value.
For Eg:
I have a table’ senario1_SRC ‘with 3 columns. col1, col2, col3.
From the below query i will get ouput as ‘col1′
select top 1 column_name from information_schema.columns where table_name=’senario1_SRC’
Next I need the data from above resulted columns.
Like below
SELECT
(select top 1 column_name from information_schema.columns where table_name=’senario1_SRC’)
FROM senario1_SRC
If I fire the above query I should get data of the column.
But I am getting col1 as result
Can you please help me on above issue.
Brilliant thank you, I used this to find the junction table for two columns in a very large database I was not familiar with by changing the WHERE clause to:
WHERE c.name LIKE ‘%FIRSTCOLUMN%’
OR c.name LIKE ‘%SECONDCOLUMN%’
thank u sir.
Hello, I am having problems. I need to bring up two different data. Like for example Title and all the pages less then 250 and only those two how can I do it. The table is called books.
Is there an equivalent Query for an IBM DB2 database?
I have several tables in a database, which all start with the name Products and I would like to be able to do a search for all ‘part numbers’, and all ‘descriptions’ (which are both columns contained in all of the databases) and then display all of the information in a single table or view.
This may help you
Hello sir,
If there are 50 columns in a table and i want display only 48 columns , how it can be done without writing the 48 columns in select query. Is there any way to find this? Please reply me. [email removed]