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
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