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
thank a lot for ur help
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!!!
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
select * from DB2.information_schema.columns
where table_name=’some table’
Thanks for a script…..
Its very Nice…
Hi,
How to find a particular record from database tables?
Thanks in advance..
Select columns from table
where col=’some value’
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.
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
Getting an error
Help me please..
SQLSTATE = S0002
[Sybase] [ODBC Driver][Adaptive Server Anywhere]
Table ‘tables’ not found
Continue ?
Thanks
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
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.
Refer this post. This exactly does what you want to acheive
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
Refer this
Hi Atif Shehzad
that is good but performance point of view pinaldave
is good
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!
Can you post the exact code you used? Make sure to note that these codes will not work in versions below 2005
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!
Thanks!
Our database queries solve my problem.
Thanks for the script. It worked very well.
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…
No. They are related
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
Below Query also works……..
select * from information_schema.Columns where column_name Like ‘%Column_Name%’
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
kindly anyone could share MCTS 70-564 dumps??
Thanks it’s useful to me
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.
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