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.

Tuesday, October 17, 2006

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


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


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.

Tuesday, October 3, 2006

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(, 32) col_name, left(, 32) col_type, typ.length
from syscolumns col
left join sysobjects tab on =
left join systypes typ on typ.xtype = col.xtype
where = 'TABLE NAME' and tab.xtype = 'U'