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 to find Sys.types column name. Please help any one..
Can i write an sql to look for a particular account number in all the tables.?
If all the account number fields in all the tables in the Db are called Account_Num and i want a list of tables that have the Account_Num = ’45’.
Sebas, I don’t know if you have a solution or not, but I was doing this exact thing a few minutes ago. This isn’t the best, but it is quick and easy. In my query below, you’ll need to update the @Column value and @Value values. Run the query into the “Results as Text” window. Copy all of the IF EXISTS lines to a new query window and run it. Enjoy! John
DECLARE @Column VARCHAR(30) = ‘BACHNUMB’
DECLARE @Value VARCHAR(30) = ”’12-09-20 B”’ — Note the extra quotes (‘) because I’m looking for character vaue. ’10’ will work if you are looking for a number
SELECT ‘IF EXISTS(SELECT * FROM ‘ + sys.tables.name + ‘ WHERE ‘ + sys.columns.name + ‘ = ‘ + @Value + ‘) BEGIN PRINT ”’ + sys.tables.name + ”’; SELECT * FROM ‘ +
sys.tables.name + ‘ WHERE ‘ + sys.columns.name + ‘ = ‘ + @Value + ‘ END’ AS table_name
FROM sys.tables
JOIN sys.columns ON sys.tables.OBJECT_ID = sys.columns.OBJECT_ID
WHERE sys.columns.name LIKE @Column
ORDER BY table_name;
Thanks alot…!!!!
find-a-particular-text-from-all-tables-in-db
pls help me to find out
This i belive consumers less query execution time..
select TABLE_NAME as ‘table name’, COLUMN_NAME as ‘column name’
FROM INFORMATION_SCHEMA.COLUMNS
order by table_name
Awesome, exactly what I needed today. Thansk!
this is what im looking for… THANKS!
dear all
is it possible to get all columns from a table except some specific one like::
select * from table where column_name column_name ;
i need to retrieve 8 columns out of 10 from a table
Thanks for this, very helpful when working your way into an existing system that is not designed properly.
Awesome, saved me some time, this is just what I was looking for! =)
I have table name as @Table_Name
I have column value as @Value but dont have Column name ( but that exist at 1st position )
how can I compare that table column name value ?
I want something like
SELECT * FROM @Table_Name
WHERE Table.Column[1].Value = @Value
How can I do that ……
Why do you want to do this? This does not sound a way to go
I have table name as @Table_Name
How can I get 1st Column Name from MyTable(@Table_Name) without knowing Column name ..??
DECLARE @column_name varchar(20)
@column_name = SELECT top 1 column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘bb_match’
select * from bb_match
where @column_name = 6
this is not working ..where is mistake ???
This is showing only column name not showing data ??
DECLARE @column_name varchar(20)
set @column_name = (SELECT top 1 column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘bb_match’)
select * from bb_match
where @column_name = ‘8’
how can show data or this record ???
You need to use Dynamic SQL which is not recommended in this case
DECLARE @column_name varchar(20)
DECLARE @sql varchar(20)=”
set @column_name = (SELECT top 1 column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘bb_match’)
set @sql=’select * from bb_match where ‘+@column_name+’ = ‘’8”’;
exec(@sql)
how to find all the tables in a database which contains a column referenced from a particular table in the databse?
Hi Pinal – I’m looking for a query which will return me database name, table name, column name for a search value. e.g. I want to search for a value = “test rating” across all tables in the database.
I have some queries, but they return values for only user created tables and not system tables or tables created by an asp.net web application for instance. Any help is appreciated!
Thank you.
The basic script has saved me from having to manually trawl through a 2GB database with over 500 tables.
You rock Pinal. I always check your blogs first when I have SQL Server questions as your answers are always easy to understand and straight to the point.
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