How to Search for Columns in SQL Server

There are several ways to look for a column in your SQL Server database.

Alternative 1: INFORMATION_SCHEMA.COLUMNS (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @colName NVARCHAR(30)
SET @colName = ‘name’

SELECT
TABLE_CATALOG AS ‘Database’,
TABLE_SCHEMA AS ‘Schema’,
TABLE_NAME AS ‘Table’,
COLUMN_NAME AS ‘Column’
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE ‘%’ + @colName + ‘%’

[/sql]

Alternative 2: sys.columns (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

SELECT
sys.tables.name AS ‘Table’,
sys.columns.name AS ‘Column’
FROM
sys.columns
INNER JOIN sys.tables
ON sys.tables.object_id = sys.columns.object_id
WHERE
sys.columns.name LIKE ‘%’ + @colName + ‘%’

[/sql]

Alternative 3: Object Search (SSMS)

In SQL Server 2000, there was an “Object Search” tool in Query Analyzer to search for objects.

To get to this tool in SQL Server 2000, either
– Go to Tools > Object Search, or
– Press F4

Unfortunately this was removed in SQL Server 2005.

But back again in SQL Server 2008:
– Go to View > Object Explorer Details
– Type object name in the Search bar
– Press Enter to Search

Yay!

Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s