:::: MENU ::::

Search for a column in all tables of a database

  • Aug 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts, T-SQL Tips and Tricks

Search for a column in all tables of a database

Today i read an interesting question on a blog, that how to search for a column in all tables of a database. Following script was given by author to search for columns with name like employeeID in adventureworks database.

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  

It served the purpose but i have a more simple approach for same purpose. My script generates same result in a more simple and flexible way

 
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  

So both may be used as convenient.

Consult us to explore the Databases. Contact us