SQL SERVER – Query to Find Column From All Tables of Database

« SQL SERVER – 2005 – Get Field Name and Type of Database Table

SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle »

SQL SERVER – Query to Find Column From All Tables of Database

August 6, 2008 by pinaldave

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 (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 81 Comments

81 Responses

on August 6, 2008 at 9:52 am | Reply Atif Shehzad

We may also get same information through following script
——————————————————————–
USE AdventureWorks
GO
SELECT Table_Schema, Table_Name, Column_Name, Data_Type
FROM information_schema.columns
WHERE table_name in ( select name from adventureworks..sysobjects
where xtype = ‘U’ )
and column_name like ‘%EmployeeID%’
order by table_schema, table_name
——————————————————————-
Take crae
on August 6, 2008 at 12:58 pm | Reply Gabriel

For me both solution don’t work :(

I receive this error :
Msg 195, Level 15, State 10, Line 2
‘SCHEMA_NAME’ is not a recognized function name.

I’m not DBA, than no access to the server but I can tell you it’s SQL Server 2005 ENTR + SP2 on my side I have “Microsoft SQL Server Management Studio”
on August 6, 2008 at 1:10 pm | Reply Gabriel

Sorry, the server where I tried to execute this query is a ….. 2000 version. Thanks for your work
on August 6, 2008 at 4:54 pm | Reply David S

I am looking for a way to automatically document all the tables in my database as well as the relationships between these tables. It seems that I could start to do this via querying sys.tables and information_schema.columns, but before I started down this path, I was wondering if you could recommend a best practice for accomplishing this or recommend a software program that does this well.

Thanks in advance for your help this, and keep up the good work with the site. It is an invaluable resource for me.

David
- on June 21, 2010 at 8:05 pm | Reply Madhivanan
  
  You should make use of Generate Script option from Management studio
on August 7, 2008 at 12:05 am | Reply David B

You can also get other objects (Tables/Views/Functions) when with the following (should work on sql2000 too):

select
o.name as tableName,
c.name as ColumnName,
o.type as ObjectType,
u.name as SchemaName
from syscolumns c
inner join sysobjects o
on o.id=c.id
inner join sysusers u
on u.uid= o.uid
where
c.name like ‘%EmployeeID%’
- on October 11, 2010 at 12:52 pm | Reply Sriraman
  
  Thanks . It was very helpful.
  - on June 29, 2011 at 1:29 pm | Reply subramaniyan
    
    thanks a lot…
on August 13, 2008 at 10:09 am | Reply shiwani

select c.name, c.id, o.name from syscolums c join sysobjects o on o.id=c.id where c.name like ‘%employee%’
on August 13, 2008 at 8:23 pm | Reply mahi

Hello,

I need to find the database the table and the column belong to in addition to the schema. Can someone pl. tell me how can I get this information?

Thank you,
Mahi
on August 20, 2008 at 1:10 pm | Reply Glenn Sørensen

Hi mahi,

Chekout the column “TABLE:CATALOG” in the information_schema.tables view.

SELECT * from information_schema.tables

Hope This Helps
/Glenn
on August 20, 2008 at 1:11 pm | Reply Glenn Sørensen

The column is TABLE_CATALOG and not TABLE:CATALOG

Sorry for the typo

/Glenn
on August 22, 2008 at 1:11 pm | Reply Mohan

how to find the available databse in server and how get tables and its details for each databse? Please anybody have any idea???

Thanks in advance
Mohan.V
- on March 23, 2010 at 3:48 pm | Reply Madhivanan
  
  Available databases
  
  EXEC sp_databases
  
  Available tables
  
  EXEC sp_tables
on August 22, 2008 at 5:26 pm | Reply Pushpa

select * from sysobjects where id in(select id from syscolumns where name like’empid%’)

The above query will return all columns starting with empid.

Thanks,
Pushpa
- on June 21, 2010 at 8:08 pm | Reply Madhivanan
  
  You can also use
  
  select * from information_schema.columns
  where column_name like 'empid'
on August 28, 2008 at 10:29 am | Reply tanvi

how we can see specific column in sql database?
- on December 25, 2009 at 1:16 pm | Reply Pradip
  
  select column_name,* from information_schema.columns
  where table_name = ‘Table_Name’
on September 22, 2008 at 4:20 pm | Reply suresh

Can anyone tell how to find the list of tables in a database server.??

Thank u in advance……..!!!!!!!!
- on March 18, 2010 at 2:48 pm | Reply Madhivanan
  
  EXEC sp_tables
on January 2, 2009 at 10:21 pm | Reply Ram

you are awesome !
on January 29, 2009 at 12:22 pm | Reply Manish K

Hi Suresh,

Try

select * From sys.tables
select * From sysobjects where type=’u’
on August 4, 2009 at 7:51 pm | Reply praveen

Hi Pinal,

Is there a way to query an entire server to find a column from all the tables of all databases?
am creating an application where users will be searching for columns in all databases of our server. the query should return column name,data type,database name,table name.
any suggestions will be greatly appreciated..

Thanks,
Praveen
on November 7, 2009 at 2:30 am | Reply Mishka

Thanks so much! That is very helpful.
on January 26, 2010 at 1:09 am | Reply Tushar

Thanks a Lot…!!!!
on February 10, 2010 at 9:46 pm | Reply TheTucko

Maby easier way with more data:

SELECT *
FROM Information_Schema.Columns
WHERE COLUMN_NAME like ‘%YOURCOLUMN%’
- on February 11, 2010 at 1:36 pm | Reply Madhivanan
  
  Yes. Also you dont need to query on system tables directly
on March 12, 2010 at 11:47 pm | Reply Ashutosh

I want to update the same column from multiple tables in database in sql server 2005.Please help in case of following query.

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;

I want to update EmployeeID from all tables which returns by above query.
- on March 13, 2010 at 11:54 pm | Reply Marko Parkkola
  
  You need to use dynamic SQL and table valued variable would be nice also. Consider following (sorry, didn’t have time to test this):
  
  – Create table to hold schema and table names
  DECLARE @t TABLE(Row int identity(1, 1), SchemaName nvarchar(max), TableName nvarchar(max))
  
  – Get schema and table names from metadata
  INSERT INTO @t
  SELECT SCHEMA_NAME(schema_id), t.name
  FROM sys.tables AS t
  JOIN sys.columns c ON c.object_id = t.object_id
  WHERE c.name LIKE ‘%EmployeeID%’
  
  – These are used in iterating through the table
  DECLARE @max int, @i int
  SELECT @i = 1, @max = MAX(Row) IN @t
  
  – These are used in dynamic SQL
  DECLARE @sql nvarchar(max)
  DECLARE @params nvarchar(max)
  
  – @newValue is the value entered into EmployeeID
  SET @params = N’@newValue int’
  
  – Loop de loop
  WHILE @i <= @max
  BEGIN
  – Construct dynamic SQL query
  SET @sql = N'UPDATE [' +
  (SELECT SchemaName FROM @t WHERE Row = @i) +
  N'].[' +
  (SELECT TableName FROM @t WHERE Row = @i) +
  N'] SET EmployeeId = @newValue'
  
  – Execute query
  EXEC sp_executesql @sql, @params, @newValue
  
  – Increment iterator
  SET @i = @i + 1
  END
on March 26, 2010 at 3:09 pm | Reply tijo

thank a lot for ur help
on April 6, 2010 at 10:52 pm | Reply Paul Mc

How would you drill down to get the get the column’s value?

For instance, if I wanted to search all of the columns in a DB looking for the term ‘%Smith%’, how do I reference the columns?

thanks!!!
on April 16, 2010 at 6:36 am | Reply Mukesh

Hi Pinal,

I am using database DB1 and want to find all columns of a table of another database DB2.
I am trying to do this by Stored procedure but not getting any solutions. Kindly Help me out.

Thanks,
Mukesh
- on June 21, 2010 at 1:52 pm | Reply Madhivanan
  
  select * from DB2.information_schema.columns
  where table_name=’some table’
on June 4, 2010 at 6:04 pm | Reply Shekhar R. Gurav

Thanks for a script…..

Its very Nice…
on June 21, 2010 at 12:03 pm | Reply babu

Hi,
How to find a particular record from database tables?
Thanks in advance..
- on June 21, 2010 at 1:51 pm | Reply Madhivanan
  
  Select columns from table
  where col=’some value’
  - on June 22, 2010 at 12:12 pm | Reply babu
    
    Hi Madhivnan,
    
    Thanks for ur answer….but i didnot ask for column….i need a particular record in a table….even i dont know the name of that table….for example: in employee table there is emp Column…in emp table there is a record name ‘RAJ’ …now i need to get ‘RAJ’ …i dont know employee table and emp column ..but i need record ‘RAJ’
    Thank You.
    - on June 22, 2010 at 1:25 pm | Reply Madhivanan
      
      Ok. Refer this post. It exactly does what you need. It searches for a particular value in all the tables and list out the table and column names if searched data are found
      http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
on August 3, 2010 at 9:24 pm | Reply Rupesh

Getting an error
Help me please..
SQLSTATE = S0002
[Sybase] [ODBC Driver][Adaptive Server Anywhere]
Table ‘tables’ not found
Continue ?

Thanks
on August 4, 2010 at 7:02 pm | Reply Rupesh

Hiii,,,

I found the solution for my above said issue.

For Sybase ASA

SELECT table_name
FROM sys.systable, sys.syscolumn
WHERE sys.systable.table_id = sys.syscolumn.table_id AND sys.syscolumn.column_name = ‘COLUMN_NAME’
ORDER BY table_name ASC;

Thanks
on October 4, 2010 at 7:45 pm | Reply Yogesh

Hi,

I want to find all the tables in the adventureworks where the column employee id having the value ’123′ exists…

basically i want all the tables in a database where a particular column with a particular value exist.
- on October 5, 2010 at 1:09 pm | Reply Madhivanan
  
  Refer this post. This exactly does what you want to acheive
  http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
- on May 16, 2011 at 3:59 pm | Reply Yogesh
  
  Hi,
  
  I want to retrieve the row counts of all the tables present in a database using a query. Please help me.
  
  Example
  
  if there are 2 tables in a database called A and B having 15 and 20 results respectively. I want a query that gives
  this display
  
  TableName RowCount
  A 15
  B 20
  
  it shoudl extract the names and row count itself from sys.tables.
  
  hope m clear.
  
  Regards,
  Yogesh
  - on May 16, 2011 at 4:01 pm | Reply madhivanan
    
    Refer this
    http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
on October 22, 2010 at 11:21 am | Reply Ranjith

Hi Atif Shehzad
that is good but performance point of view pinaldave
is good
on October 25, 2010 at 10:35 pm | Reply khan1

all of these queries throw following error for me:

Category Timestamp Duration Message Line Position
Error 10/25/2010 1:03:34 PM 0:00:00.000 SQL Server Database Error: Incorrect syntax near ‘‘’. 58 0

line 58 is : where c.name like ‘%EmployeeID%’

not sure what’s wrong here :S …. can anyone help???

Thanks!
- on October 26, 2010 at 1:48 pm | Reply madhivanan
  
  Can you post the exact code you used? Make sure to note that these codes will not work in versions below 2005
on October 26, 2010 at 12:36 am | Reply khan1

ok, i got this query working but it only lists column names from the dbo schema. i know i have at least one more schema in the database but its not showing any of the fields/tables from that schema.

select o.name as tableName, c.name as ColumnName, o.type as ObjectType, u.name as SchemaName
from syscolumns c inner join sysobjects o on o.id=c.id
inner join sysusers u on u.uid= o.uid

any ideas?

Thanks!
on October 28, 2010 at 11:46 am | Reply Ravindra

Thanks!
Our database queries solve my problem.
on November 23, 2010 at 10:13 pm | Reply Jeff

Thanks for the script. It worked very well.
on December 9, 2010 at 2:18 am | Reply Brad

This DOES NOT WORK.

“FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID”

Those 2 object_id values have no relationship to one another…
- on December 9, 2010 at 4:30 pm | Reply Madhivanan
  
  No. They are related
  - on December 10, 2010 at 1:47 am | Reply Brad
    
    Very sorry. I was getting confused somehow by trying to query all foreign key constraints along with their table/column names. Here’s the query in case somebody else stumbles across this:
    
    select
    object_name(fk.constraint_object_id) ‘ConstraintName’,
    t.name ‘TableName’,
    c.name ‘ColumnName’
    from sys.foreign_key_columns fk
    inner join sys.tables t on fk.parent_object_id = t.object_id
    inner join sys.columns c on t.object_id = c.object_id
    and fk.parent_column_id = c.column_id
    order by ConstraintName
on December 20, 2010 at 5:05 pm | Reply Raj

Below Query also works……..

select * from information_schema.Columns where column_name Like ‘%Column_Name%’
on December 21, 2010 at 4:27 am | Reply aseem

hi,

i have multiple databases with the names:

TOY_1 (contains approx 120 tables)
TOY_2 (contains approx 150 tables)
TOY_3 (contains approx 100 tables)

….
on SQL srever 2005.

My question is: Is there a way that i can search by column name across all these databases. For eg:- if there is a column called purchase_id in table XXX of DB TOY_2, but without being aware that in which DB does this coulmn lie, can i fire a query which will search all the tables in all the DBs ( TOY_1, TOY_2,…..) and bring me back the details as to in which Db’s, which table does the column lie?

I know how to search column name when searching a single database, but i don’t know how to search across all the databases?

thanks
aseem
on December 21, 2010 at 3:25 pm | Reply Shreya

kindly anyone could share MCTS 70-564 dumps??
on February 4, 2011 at 6:07 pm | Reply csreddy

Thanks it’s useful to me
on February 5, 2011 at 7:21 am | Reply price

I have seen SEVERAL of your examples over the years and it is an understatement to say THANK YOU VERY MUCH. You have been very helpful and please keep up the good work.
on February 14, 2011 at 10:31 pm | Reply Selvam

there are > 200 DB in one instance. I want to find out with one query, dbname,schemaname,tablename,column names, column type. Could not which system tables have the relationships

Your help is appreciated,(my mail id: emailid removed)

Thanks
on March 9, 2011 at 2:48 am | Reply gregory mertens

thanks for the info. This saved me a lot of time trying to find a known column in a large set of tables.
on March 21, 2011 at 7:05 pm | Reply Sheetal

Hi,

Need help to fetch records. I have a table product having 2 field product_id and features. I have 5 following row

1 Camera
1 Bluetooth
1 MP3
2 Camera
3 Camera
3 MP3

Now I just want to fetch thoes products, having Camera,Bluetooth and MP3

Please suggest me to build a query

Thanks
Sheetal
- on March 22, 2011 at 12:33 pm | Reply madhivanan
  
  select product_id from table
  where product_name in (‘Camera’,'Bluetooth’,'MP3′)
  group by product_id
  having count(distinct product_name)=3
on March 22, 2011 at 4:12 pm | Reply Sheetal

Hi Madhivanan,

Thanks very much for your quick response. Best part is I have tried the query as you suggested and its working. Really thanks for your help.

Sheetal
on April 5, 2011 at 12:10 pm | Reply Shail

hi ,
for eg.
create view abc as
SELECT
a.pen,
b.book
from account a, books b

now i want to know the name of all those views who have books in their FROM clause. Plz suggest me to build the query.
Thanks in Advance
on April 6, 2011 at 7:28 pm | Reply Shaik Fayaz

Hello my dear friends

I have simple code that is to get a data from couple of tables. But it showing the Result multiple times.Can any body help me.
THIS IS THE CODE.
———————————————————-
SELECT H1.EMP_CODE,H1.NAME1A+’ ‘+H1.NAME2A+’ ‘+H1.NAME3A ‘NAME’
,H2.DESCA ‘JOB’
,H3.DESCA ‘COUNTRY’
FROM H_EMP H1 INNER JOIN H_D_JOB H2
ON H1.JOB_CODE=H2.CODE
INNER JOIN H_D_NATIONALITY H3
ON H1.NAT_CODE=H3.CODE
WHERE EMP_CODE=7515
——————————————————
OUT PUT
——————————————————–
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
7515 شيخ فياز باشا مبرمج هندي
I’m really Thanks alot to help me.
on April 27, 2011 at 8:08 pm | Reply Tom

I have used TM Field Finder for a while and it’s really helpfull when you need to search in SQL Server database for particular text.
on May 3, 2011 at 6:56 am | Reply Kumar

CREATE VIEW [Sales].[vSalesPerson]
AS
SELECT
s.[SalesPersonID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,[JobTitle] = e.[Title]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,[StateProvinceName] = sp.[Name]
,a.[PostalCode]
,[CountryRegionName] = cr.[Name]
,[TerritoryName] = st.[Name]
,[TerritoryGroup] = st.[Group]
,s.[SalesQuota]
,s.[SalesYTD]
,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
INNER JOIN [HumanResources].[Employee] e
ON e.[EmployeeID] = s.[SalesPersonID]
LEFT OUTER JOIN [Sales].[SalesTerritory] st
ON st.[TerritoryID] = s.[TerritoryID]
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

Query is : How could we say that the above VIEW follows which type of JOIN by looking at resultset or View ?
on May 12, 2011 at 3:44 pm | Reply S.Thomas

Is there any query to find all the references of a particular column in a table? In other way how to find where and all that particular column (not the name, actual column refernce) is used? (ex: sps, user function, FK , views etc)
- on May 16, 2011 at 4:00 pm | Reply madhivanan
  
  Query on definition colum of the system table sys.sql_modules
on May 13, 2011 at 2:25 pm | Reply Venkat

Hi,

I want to retrieve some of cloumn names form table..is that possible ?

Thanks

Venkat
- on May 16, 2011 at 3:50 pm | Reply madhivanan
  
  Select column_name from information_schema.columns where table_name=’table name’
on May 27, 2011 at 12:55 pm | Reply brutus

if you have a ntext column is does not work….
Example :

if i want to search for any columns that contain value:%SKUS% it doesn’t show anything…
on June 9, 2011 at 3:07 pm | Reply satheesh

Hi friends

I know only DBMS concepts
give me tips to know SQL
on June 9, 2011 at 4:08 pm | Reply satheesh

hi friends
on June 9, 2011 at 4:09 pm | Reply satheesh

i need your help to study SQL
on June 15, 2011 at 10:32 am | Reply caroline

I’m not a big commenter, but I just wanted to say thanks. I end up finding answers on your blog really often, and I really should take the time to say thank you.

Soo…thanks for being so concise, and for taking the time to share your knowledge, it is much appreciated.
on July 7, 2011 at 9:22 pm | Reply SheldonL

This script is awesome. Thanks for sharing with the community. I finished my work alot faster now.
on October 13, 2011 at 10:57 am | Reply Parthiban Sekar

Hi Guys,

I often come across the situation where i need to find the column name having particular value of unknown table in a Sql database. For example, I know the value of a column like ‘Test’ and i need to find the Column name and table name in the particular data base. Please share if i can query like that.

Thanks and Regards,
Parthiban Sekar
on November 8, 2011 at 11:48 pm | Reply Developer Mike

You saved me more than million times. Thank YOU!
on December 12, 2011 at 3:54 pm | Reply manoj

HI,

I need query which will find common tables from different databases..?
- on December 15, 2011 at 7:20 pm | Reply madhivanan
  
  Example code
  
  exec sp_msforeachdb ‘if exists(select * from ?..sysobjects where name=”TABLE_PRIVILEGES”) select ”TABLE_PRIVILEGES”’