How do I list or search all the column names in my database in the SQL server?

787 Asked by ankur_3579 in SQL Server , Asked on Jul 12, 2021

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? How to find column names in SQL server?

Answered by anu rhea

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/

SQL server find column name using these queries

SELECT COLUMN_NAME.
FROM INFORMATION_SCHEMA. COLUMNS.
WHERE TABLE_NAME = 'Your Table Name'
ORDER BY ORDINAL_POSITION.

How do I list or search all the column names in my database in the SQL server?

Your Answer