Find Column Names across a SQL Database

When working against databases that you’re not particularly familiar with and you don’t have a data dictionary (or any schema information) you often need to have a quick way to find column names in a database.  

Using this query I created, you can specify a value for the @FindColumn parameter and it will look for that column using the database of your choice. In this example, I looking for the 'clientkey' across the database ProdDB.

 

-- FIND  clientkey columns in SQL Database ProdDB--

 

Use ProdDB
Declare @FindColumn varchar(32)
SET @FindColumn = 'clientkey'

 

SELECT t.Name AS TableOrViewName, [type_desc] [Type],

SCHEMA_NAME(schema_id) AS [Schema],

c.name AS ColumnName

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE @FindColumn

UNION

SELECT v.name,type_desc, SCHEMA_NAME(schema_id) AS [Schema] , cl.name

 FROM sys.views v

 INNER JOIN sys.columns cl ON v.OBJECT_ID = cl.OBJECT_ID

 WHERE cl.name LIKE @FindColumn 

 ORDER BY [Schema], TableOrViewName