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.

SQL Server: How to do a fuzzy search

I was asked yesterday to do a fuzzy search in SQL Server. The request was to find some numbers in a field that fell within a certain tolerance.

I worked out how to do this pretty simply using a BETWEEN clause with a fuzziness factor to go with it. In a simplified example, I'm looking for all employees within 3 years of 30. I parameterized both the age and the fuzz factor to make changes to this simple:

-- Here I just create a temporary table for the example.
create table #employee (employee varchar(32), age int)
insert into #employee values ('John', 32)
insert into #employee values ('Mary', 24)
insert into #employee values ('Sam', 40)
insert into #employee values ('Sarah', 28)
insert into #employee values ('Charles', 29)
insert into #employee values ('Henry', 31)

-- Here I declare variables to hold the age to look for
-- and the "fuzziness" of the search
declare @fuzz int
set @fuzz = 3
declare @seek_age int
set @seek_age = 30

-- This is the actual search
select employee, age
  from #employee e
 where e.age between @seek_age - @fuzz and @seek_age + @fuzz

Employee	Age
John	32
Sarah	28
Charles	29
Henry	31

Increasing the @fuzz value increases the possibility of finding records.

In the actual case, I used the BETWEEN clause as the joining clause in a left join to fuzzily join records. This looks pretty scary but works. The following example does this to create a list of employees matched to other employees within 3 years of the same age:

-- Find other employees within three years of an employee's age
declare @fuzz int
set @fuzz = 3

select test.employee test_employee, test.age test_age,
       fuzzy.employee fuzzy_employee, fuzzy.age fuzzy_age
  from #employee test
  left join #employee fuzzy
    on test.age between fuzzy.age - @fuzz and fuzzy.age + @fuzz
   -- here we don't want an employee to match him or herself
   and test.employee <> fuzzy.employee

test_employee	test_age	fuzzy_employee	fuzzy_age
John	32	Charles	29
John	32	Henry	31
Mary	24	NULL	NULL
Sam	40	NULL	NULL
Sarah	28	Charles	29
Sarah	28	Henry	31
Charles	29	John	32
Charles	29	Sarah	28
Charles	29	Henry	31
Henry	31	John	32
Henry	31	Sarah	28
Henry	31	Charles	29

A NULL result mean that that employee has no co-workers with 3 years of their age. Again, the @fuzz factor can be increased to find more matches.

SQL Server: How to list the columns in a table

This is a simple script to list the columns in a given table in SQL Server. It uses the SYSCOLUMNS, SYSTYPES, and SYSOBJECTS system tables:

use mydatabase

select left(col.name, 32) col_name, left(typ.name, 32) col_type, typ.length
  from syscolumns col
  left join sysobjects tab on tab.id = col.id
  left join systypes typ on typ.xtype = col.xtype
 where tab.name = 'TABLE NAME' and tab.xtype = 'U'

go