Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
similar question asked in SO –  Coder Hawk Jan 11 '11 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 '11 at 14:40
add comment

3 Answers 3

up vote 20 down vote accepted

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

USE AdventureWorks 
GO 
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
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

http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

share|improve this answer
add comment

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

alt text

share|improve this answer
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 '11 at 14:06
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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