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
How can I search a value in a particular table without knowing column name? Would it be possible to get table name, column name and schema name it is in?
Hello Pinal, is there any way to use sql queries to determine possible relationships between databases ( I know you cannot enforce key constraints between databases). probably querying the statistics object of both databases and matching column data between databases?
Hi i am looking all blank column from all tables of a server.
Please help.
I need to extract all blank column from all tables of a single sql database..
Hi Pinal,
Noticed a small correction. in the order by, it should be t.name instead of t.table_name.
No need. In ORDER BY Clause alias name will work perfectly
Thanks….it helped me.
Hi Pinal,
Great blog. I visit here often through google looking for answers.
This time, I couldnt find one…
I am looking for a specific column name across all tables and all databases in SQL Server.
Can you please help!?
That helped. Thank you.
Hi Pinal,
now how do i rename this column from all the tables?
Hi Pinal
How can i get all blank column list from particular table with single query
Hello pinal sir,
i fetch table name from another table column and store table in variable.
eg.
declare @TableName nvarchar(50);
select @TableName=Table_Name from TableRelation where id=1;
print @TableName;
how to select table base on table name which are assign in @TableName;
You need to use Dynamic SQL which is not recommended in this case
select @TableName=’SELECT * FROM +@TableName;
EXEC(TableName);
Great Script. Saved me lots of time today. Thank you old friend.
Search all over for this kind of script. Yours is the only one that works. Thanks!
Hello, Have you ever do this in a linked server? discard previous comment
Thanks a lot !! I always get answers from your posts .
Find all tables containing column with specified name within linked server
Find all tables containing column with specified name in linked server of sqlserver
Thanks for this. Very useful.
Hi, Im trying to run this query on multiple servers but still getting error ” invalid column_name” any suggestions, if thes query can work with sp_MSforeachdb
Pinal sir, please help me find out list of columns/tables in a database based on a given column value.
running through migration and due to nomenclature changes, couldn’t able to figure out corresponding attribute.
Hoping if i can matchup based on column value, can match up.
I really wish I can help Mohan, this is more of consultation or hands-on experience.