How do I list or search all the column names in my database?
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?
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.
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
There is also SQL Search - a free tool that integrates with SQL Server Management Studio.
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!
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.
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.