I want to search for a string in the names of the columns present in a database.

I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking for a quick way to do this.

What do you recommend?

  • similar question asked in SO
    – CoderHawk
    Jan 11, 2011 at 6:08
  • good article. Thanks a lot. I also used TM Field Finder and it's really powerfull in matter of search in SQL Schema and also data.
    – user1600
    Apr 27, 2011 at 14:40
  • Related: Exploring your database schema with SQL Aug 26, 2015 at 22:24

4 Answers 4


You can use following query to list all columns or search columns across tables in a database.

USE AdventureWorks 
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name; 

You can make use of information_schema views to list all objects in SQL Server 2005 or 2008 databases.

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.columns



There is also SQL Search - a free tool that integrates with SQL Server Management Studio.

alt text

  • 1
    SQL Search is a good tool, but keep in mind that the results window is arbitrarily limited to, I believe, 250 results. The last time I used this tool, Red Gate did not have a way of lifting this limitation. That having been said, it is still a very useful tool and I would recommend it to any SQL Server DBA or Developer. The price is right too!
    – Matt M
    Jan 11, 2011 at 14:06

Late one but hopefully useful since both tools are free.

ApexSQL Search – good thing about this tool is that it can also search data, show dependencies between objects and couple other useful things.

enter image description here

SSMS Toolpack – free for all versions except SQL 2012. A lot of great options that are not related only to searching such as snippets, various customizations and more.

enter image description here


This is one that all SQL DBA from old time use.

EXEC sp_help 'your table name';

If only with single line, you don't even need to type EXEC. Just do

sp_help 'your table name'

Not the answer you're looking for? Browse other questions tagged or ask your own question.