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
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.