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 tab.name table_name, col.name column_name
from sysobjects tab
left join syscolumns col on tab.id = col.id and tab.xtype = 'U'
where col.name like '%current%balance%'
order by 1,2


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

2 comments:

  1. Tim,

    This script just saved us! Thanks!

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

    ReplyDelete

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.