How do you find one column from all tables of the database? How many tables in AdventureWorks have a column called EmployeeID? It came up while I was writing Difference Between INTERSECT and INNER JOIN, and it turned out to be a handy script to have.
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.
If you want to find all the column names from your database, run the same script without the WHERE clause.
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;
Add the data type while you are there
Nine times out of ten, the reason you are hunting for a column across tables is that you are about to change it, or you are wondering why a join is slow. Both questions need the type, not just the name.
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
ty.name AS DataType,
c.max_length,
c.is_nullable
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.types AS ty ON c.user_type_id = ty.user_type_id
WHERE c.name LIKE '%EmployeeID%'
ORDER BY SchemaName, TableName;Run this across a database you have inherited and you often find the same column defined three different ways: INT in one table, BIGINT in another, VARCHAR in a third. That is usually the reason a join runs badly, and it is very hard to spot any other way.
Views and procedures have columns too
sys.tables only returns tables, which is exactly what the question asked for. But if you are looking for everywhere a column appears, views are part of the answer.
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS ObjectName,
o.type_desc AS ObjectType,
c.name AS ColumnName
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE c.name LIKE '%EmployeeID%'
AND o.type IN ('U', 'V')
ORDER BY o.type_desc, SchemaName, ObjectName;U is a user table and V is a view. Swapping sys.tables for sys.objects is the only real change.
The standard version
There is an INFORMATION_SCHEMA version of this, and it is worth knowing because it works on other database engines too.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%EmployeeID%' ORDER BY TABLE_SCHEMA, TABLE_NAME;
Use this one if your script has to travel. Use the sys version on SQL Server, because it knows about things the standard views do not, like whether a column is an identity or computed.
Searching every database on the server
Sometimes you do not know which database the column is in. This walks them all.
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
SELECT ''' + name + N''' AS DatabaseName,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName, c.name AS ColumnName
FROM ' + QUOTENAME(name) + N'.sys.tables AS t
JOIN ' + QUOTENAME(name) + N'.sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE ''%EmployeeID%'' UNION ALL'
FROM sys.databases
WHERE state = 0 AND database_id > 4;
SET @sql = LEFT(@sql, LEN(@sql) - 9);
EXEC sp_executesql @sql;Two details make it safe. state = 0 skips databases that are offline or restoring, which is what breaks most scripts like this. database_id > 4 skips master, model, msdb and tempdb, which you almost never want in the results.
One thing to watch with LIKE
Searching for ‘%ID%’ will match EmployeeID, OrderID, and also Identity, Video and Guidance. If you want the column named exactly EmployeeID, drop the wildcards and use c.name = 'EmployeeID'. If you want columns ending in ID, use '%ID' with the wildcard only at the front.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
191 Comments. Leave new
thanks for the info. This saved me a lot of time trying to find a known column in a large set of tables.
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
select product_id from table
where product_name in (‘Camera’,’Bluetooth’,’MP3′)
group by product_id
having count(distinct product_name)=3
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
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
You can use sp_depends store procedure
EXEC sp_depends books
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.
Add DISCTINCT Clause After the SELECT.
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.
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 ?
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)
Query on definition colum of the system table sys.sql_modules
Hi,
I want to retrieve some of cloumn names form table..is that possible ?
Thanks
Venkat
Select column_name from information_schema.columns where table_name=’table name’
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…
Hi friends
I know only DBMS concepts
give me tips to know SQL
hi friends
i need your help to study SQL
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.
This script is awesome. Thanks for sharing with the community. I finished my work alot faster now.
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
You saved me more than million times. Thank YOU!
HI,
I need query which will find common tables from different databases..?
Example code
exec sp_msforeachdb ‘if exists(select * from ?..sysobjects where name=”TABLE_PRIVILEGES”) select ”TABLE_PRIVILEGES”’
Tnanks. I needed this to find several feilds in a new database. really helped
Hi Pinal,
I want a sql query to find all tables in a database, having three column is same in every table.