Object Search in SQL Server Management Studio

When the query writing tool of choice switched from Query Analyzer (SQL Server 2000 and below (8.0) ) to SQL Server Management Studio (SQL Server 2005+ (9.0)), along with it went the ability to search for columns in tables in a quick way.

Thankfully, there is a way to search the database you are currently in for those hard to find columns. This is particularly handy when getting to grips with new databases where you only know a bit of the column name. It's just a bit of a shame the GUI has gone.

Below is a script which will create you an SP that you can pass a partial column name. Obviously, this is geared towards a SQL Server 2005 box, change the nvarchar(max) to nvarchar(255) or something similar in SQL Server 2000.

It will return you the column which matches your search and the table its in.

Syntax for this is : EXEC TSQLColumn_Search 'search_term_here'

use
go

if exists (select 1 from sysobjects where name='TSQLColumn_Search')
begin
drop procedure TSQLColumn_Search
end
go

create procedure TSQLColumn_Search (@strColumnName nvarchar(max))
as
set transaction isolation level read uncommitted
select c.name as Column_Name
,o.name as Table_Name

from sys.all_columns c with (nolock)
inner join sysobjects o with (nolock) on o.id = c.object_id
inner join syscomments co with (nolock) on co.id = o.id
where c.name like '%'+@strColumnName+'%'
go


Enjoy!