Wednesday, October 18, 2006

SQL Server: How to show tables with a column name like...

In SQL Server, I often seek which table has a column with a name that I am not sure about. For example, I need the table and column name for a column like "current loan balance".

I use this query to find which table has a column name like...:

use loan_db

select table_name, column_name
from sysobjects tab
left join syscolumns col on = and tab.xtype = 'U'
where like '%current%balance%'
order by 1,2

The only thing you have to work out then is the LIKE clause.


  1. Tim,

    This script just saved us! Thanks!

  2. Cool! Glad I was able to help, albeit "posthumeously".


I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.