Find all SQL Columns (by tablename) containing a certain string

Often you need to quickly find an SQL database column based on a partial name, or do something like show me all the column names that have “cust” or “addr” or “date” in the column name…

<pre>
use YourDatabaseHere

select b.name as 'TableName', a.name as 'ColumnName' from sys.columns A
  inner join sys.tables B on a.object_id = b.object_id
where a.name like '%cust%'
order by b.name, a.name
</pre>

This finds all columns containing the letters ‘cust’. Just change ‘cust’ to whatever you want

Example use: I know there’s probably a column called ‘zipcode’ or ‘postal’ code, but I don’t know what they called it:

<pre>
use YourDatabaseHere
 select b.name as 'TableName', a.name as 'ColumnName' from sys.columns A
  inner join sys.tables B on a.object_id = b.object_id
where a.name like '%zip%' or a.name like '%post%'
order by b.name, a.name
</pre>

Uncategorized  

Leave a Reply