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)

About these ads

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

129 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 September 5, 2012 at 5:06 pm | Reply Joe Schmo
  
  Perfect… just what I was looking for…
- on May 7, 2013 at 1:34 am | Reply scoyne
  
  just saved me a ton of time, thanks!
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 May 20, 2013 at 8:35 pm | Reply Danish
  
  Thanks!
- on May 20, 2013 at 8:41 pm | Reply Danish
  
  Thanks @ Pushpa. In general the suggestions are really good on this site. I am a new SQL stored proc guy and trying to learn the whole logic around stored proc.
  
  I would love to connect with any of you on LinkedIn. Send me an email request at DanishJaff. I am using Gmail.
  
  Thank You.
  Danish
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 15, 2013 at 1:54 pm | Reply nagarjunareddy
  
  select * from INFORMATION_SCHEMA.tables
  
  here u can get list of tables present in the database server….!!!
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”’
on February 9, 2012 at 8:26 pm | Reply Sharky

Tnanks. I needed this to find several feilds in a new database. really helped
on February 16, 2012 at 9:56 pm | Reply Prince

Hi Pinal,

I want a sql query to find all tables in a database, having three column is same in every table.
on February 26, 2012 at 9:36 am | Reply itdev

I know only DBMS concepts
give me tips to know SQL
on February 27, 2012 at 6:51 pm | Reply KARNA

Which one is best, * or Specified column names in a Select Query?Why?
on March 26, 2012 at 11:55 am | Reply detructor

this just saved me from digging through a few thousand lines of
code. Thanks :)
on March 26, 2012 at 11:56 am | Reply detructor

this just saved me from digging through a few thousand lines of source code. Thanks.
on April 6, 2012 at 10:02 pm | Reply Alan

Thanks so much! Your blog has been very helpful to me lately! :)
on April 13, 2012 at 7:16 pm | Reply Joe Chizmas

I just wanted to finally say thanks – every time I google a tsql question, whenever your face appears, I follow the link knowing I’ll get good, solid, reliable advice. You sir, give excellent nuggets and are quite an asset to the internet at large. Thank you.
on April 24, 2012 at 11:16 am | Reply Suvendu

Pinal Sir, the 2nd best part of your blog posts is the large no. of comments, through which one can learn different ideas.
Thanks for providing us such a useful blog !!!
on April 29, 2012 at 12:13 pm | Reply wahab

I have a large database and I want to determine how many tables have a specific column with a specific value in it.
on May 18, 2012 at 12:21 am | Reply jerry

This works great and I just tried it with date and the only thing I can think of is data type…such as date datetime or smalldatetime
how would I add a column for the format of the date ?
on May 18, 2012 at 3:58 pm | Reply Johirul Islam

here is the result

SELECT Table_Schema, Table_Name, Column_Name, Data_Type
FROM information_schema.columns
WHERE table_name in ( select name from sys.objects
where type = ‘U’ )
and column_name like ‘%EmployeeID%’
order by table_schema, table_name
on May 18, 2012 at 8:15 pm | Reply jerry

Thanks Johirul that really helped.
on May 29, 2012 at 6:31 pm | Reply Pitso

Hi,

Can someone help me with a script that looks at how many tables in oracle schema that have column name like ‘EmployeeID’?

Actual I need a very same script that works like the one below but in Oracle aqua, not in ms server.

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;

Thanks in advance
on June 11, 2012 at 5:11 pm | Reply bugra

thank you works like a charm
on June 12, 2012 at 1:12 pm | Reply Kamakshi Suram

Hi Dave,

I have a question.
Is it possible for you to get data for particulat value.
For Eg:
I have a table’ senario1_SRC ‘with 3 columns. col1, col2, col3.

From the below query i will get ouput as ‘col1′

select top 1 column_name from information_schema.columns where table_name=’senario1_SRC’

Next I need the data from above resulted columns.

Like below
SELECT
(select top 1 column_name from information_schema.columns where table_name=’senario1_SRC’)

FROM senario1_SRC

If I fire the above query I should get data of the column.

But I am getting col1 as result

Can you please help me on above issue.
on June 20, 2012 at 10:02 am | Reply Tokes

Brilliant thank you, I used this to find the junction table for two columns in a very large database I was not familiar with by changing the WHERE clause to:

WHERE c.name LIKE ‘%FIRSTCOLUMN%’
OR c.name LIKE ‘%SECONDCOLUMN%’
on July 2, 2012 at 3:29 pm | Reply dinesh

thank u sir.
on July 7, 2012 at 12:19 pm | Reply vicky

Hello, I am having problems. I need to bring up two different data. Like for example Title and all the pages less then 250 and only those two how can I do it. The table is called books.
on August 1, 2012 at 12:09 am | Reply Alan

Is there an equivalent Query for an IBM DB2 database?
on August 2, 2012 at 12:51 am | Reply Steve Jones

I have several tables in a database, which all start with the name Products and I would like to be able to do a search for all ‘part numbers’, and all ‘descriptions’ (which are both columns contained in all of the databases) and then display all of the information in a single table or view.
- on August 23, 2012 at 6:20 pm | Reply madhivanan
  
  This may help you
  http://beyondrelational.com/modules/2/blogs/70/posts/10883/search-a-value-in-character-column-of-all-tables.aspx
on August 3, 2012 at 3:15 pm | Reply Lokesh

Hello sir,

If there are 50 columns in a table and i want display only 48 columns , how it can be done without writing the 48 columns in select query. Is there any way to find this? Please reply me. [email removed]
on August 4, 2012 at 12:09 pm | Reply Udhaya

How to find Sys.types column name. Please help any one..
on September 22, 2012 at 7:00 am | Reply SQL SERVER – Validating Unique Columnname Across Whole Database « SQL Server Journey with SQL Authority

[...] Additional Reference: SQL SERVER – Query to Find Column From All Tables of Database [...]
- on April 9, 2013 at 3:47 pm | Reply pathak trilok
  
  sir how to find the list of tables on which insert statement is fired means on which data is inserted date wise
  ????
on September 29, 2012 at 3:51 am | Reply Sebas

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′.
on October 12, 2012 at 1:29 am | Reply John

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;
on October 17, 2012 at 1:17 pm | Reply Unais NI

Thanks alot…!!!!
on December 13, 2012 at 12:45 pm | Reply parth

find-a-particular-text-from-all-tables-in-db
- on December 13, 2012 at 12:46 pm | Reply parth
  
  pls help me to find out
on January 1, 2013 at 2:10 pm | Reply Alkesh

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
on January 4, 2013 at 10:50 pm | Reply GroovyNorCalGal

Awesome, exactly what I needed today. Thansk!
on January 29, 2013 at 6:36 am | Reply KuroZukami

this is what im looking for… THANKS!
on February 22, 2013 at 11:54 am | Reply faisal

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
on March 1, 2013 at 3:05 am | Reply Matthew Moore

Thanks for this, very helpful when working your way into an existing system that is not designed properly.
on March 1, 2013 at 10:22 pm | Reply James

Awesome, saved me some time, this is just what I was looking for! =)
on March 2, 2013 at 9:40 pm | Reply vin

how to display queried data in vb.net 2010?
on May 10, 2013 at 11:48 am | Reply kranti

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 ……
on May 10, 2013 at 12:26 pm | Reply kranti

I have table name as @Table_Name
How can I get 1st Column Name from MyTable(@Table_Name) without knowing Column name ..??
on May 10, 2013 at 1:03 pm | Reply kranti

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 ???
on May 10, 2013 at 1:52 pm | Reply kranti

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 ???
on May 17, 2013 at 6:01 pm | Reply Pratik Aggarwal

how to find all the tables in a database which contains a column referenced from a particular table in the databse?