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. The question was how to find one column from all tables of the database.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
191 Comments. Leave new
Pinal, Thanks for all the articles you produce. Like Paul above, I too look here first for help. Using your original script, I developed this to traverse all databases in an instance. I hope it helps someone out.
/************** Drop table if it exists **************************/
USE [MASTER]
If OBJECT_ID(‘PIITable’,’U’) IS NOT NULL
DROP TABLE PIITABLE
/************** Create temp table in master **************************/
Create table PIITable(ID Int Identity (1,1),
Database_Name NVARCHAR(100),Table_Name NVARCHAR(100),SCHEMA_NAME NVARCHAR(20),
COLUMN_NAME NVARCHAR(100),Data_Type NVARCHAR(100))
/************** Create table variable to store database names **************************/
Declare @Databases table
(id int identity (1,1),DatabaseName nvarchar(100))
/************** Insert database names into table variable **************************/
Insert into @Databases
SELECT name [Database Name]
FROM [model].[sys].[databases]
where (database_id > 4 and Name Not In(‘Distribution’))
order by name
–select * from @Databases –Select all databases from table variable (For testing only)
/***************************** Set up variables for loop **********************/
Declare @row int
Declare @rows int
Declare @database_name Nvarchar(MAX)
Declare @Execdatabase Nvarchar(MAX)
/***************************** Set up loop execution *****************************/
Select @rows = (Select MAX(id) from @Databases)
Set @row = 1
While @row <= @rows
Begin
Set @database_name = (Select DatabaseName from @Databases where id = @row)
Set @Execdatabase =
'Use '+ ' '+ '['+ @database_name +']'+
'Select ('''+ @database_name +''')as Database_Name, t.name AS table_name ,
SCHEMA_NAME( t.schema_id
)AS schema_name , c.name AS column_name,sys.types.name Data_Type INTO ##PrePIITable
FROM sys.tables AS t INNER JOIN
sys.columns AS c ON t.object_id = c.object_id INNER JOIN
sys.types ON c.system_type_id = sys.types.system_type_id
WHERE c.name IN( ''SSN'', ''FirstName'' , ''LastName'' , ''First_Name'' ,''Last_Name'' , ''MiddleName''
, ''Name_First'' , ''Name_Last''
)
OR c.name LIKE ''%Form%'' OR c.name LIKE ''%addr%''
GROUP BY t.name ,
SCHEMA_NAME( t.schema_id
) , c.name,sys.types.name
ORDER BY schema_name , table_name; '
/***************** Execute above script **************************/
exec (@Execdatabase)
/********* Insert results into temporary Table (Master.dbo.PIITable) **************/
INSERT INTO Master.dbo.PIITable
Select * from ##PrePIITable
/***************** Drop temp table for next itteration of loop **************************/
If OBJECT_ID('tempdb..##PrePIITable','U') IS NOT NULL
DROP TABLE ##PrePIITable
Set @row = @row + 1
End
/***************** Select entire result set from PIITable **************************/
Select Serverproperty('Servername') as 'Server',
Isnull(Serverproperty('Instancename'),'Default')'Instance_Name',
database_Name,Table_Name,Schema_Name,Column_Name,Data_Type
from master.dbo.PIITable
Where Data_Type ‘uniqueidentifier’
Group By database_Name,Table_Name,Schema_Name,Column_Name,Data_Type
order by Database_Name
/***************** Select Database/table result set from PIITable *******************/
Select Serverproperty(‘Servername’) as ‘Server’,
Isnull(Serverproperty(‘Instancename’),’Default’)’Instance_Name’,
database_Name,Table_Name,Schema_Name,Data_Type
from master.dbo.PIITable
Where Data_Type ‘uniqueidentifier’
Group by database_name,Table_Name,Schema_Name,Data_Type
order by Database_Name
/********************** Clean up temp table in master ************/
USE [MASTER]
If OBJECT_ID(‘PIITable’,’U’) IS NOT NULL
DROP TABLE PIITABLE
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