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.