Search For Field Name In Sql Server

6 min read

Search for Field Name in SQL Server: The Complete Practical Guide

Searching for a field name in SQL Server is one of the most common tasks developers and DBAs perform when working with unfamiliar databases. Whether you are onboarding to a legacy system, debugging a broken report, or tracking down where a column like CustomerEmail lives, knowing how to search metadata quickly saves hours of clicking through Object Explorer. This guide shows you every practical method, from simple INFORMATION_SCHEMA queries to cross-database searches and value-level scans.

Why You Need to Search Column Names

Large databases often contain hundreds of tables with overlapping naming conventions. A single business concept such as "email" might appear as Email, EmailAddress, EmailAddress1, CustEmail, or Contact_Email. Without a reliable way to search, you end up guessing table names or scrolling through thousands of objects.

Common scenarios include:

  • Finding which table stores a specific field before writing a join.
  • Impact analysis before renaming or dropping a column.
  • Data discovery for GDPR, HIPAA, or PCI compliance work.
  • Documentation of an undocumented legacy schema.
  • Locating a column that appears in an error message or application log.

Method 1: INFORMATION_SCHEMA.COLUMNS (The Simplest Approach)

The INFORMATION_SCHEMA.That's why cOLUMNS view is ANSI-standard, readable, and works across most relational platforms. It is the best starting point for beginners Took long enough..

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%email%';

Key points to remember:

  • LIKE '%email%' finds the word anywhere in the name; use = 'email' for an exact match.
  • Searches are case-insensitive by default because SQL Server uses a case-insensitive collation (for example, SQL_Latin1_General_CP1_CI_AS).
  • Add AND TABLE_SCHEMA = 'dbo' to narrow results to a specific schema.
  • This view returns columns from tables and views, but not temporary tables.

To search for several naming patterns at once, use a table value constructor:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%email%'
   OR COLUMN_NAME LIKE '%phone%'
   OR COLUMN_NAME LIKE '%ssn%';

Method 2: sys.columns (The Power User Approach)

The catalog views in the sys schema expose far more detail than INFORMATION_SCHEMA, including identity flags, computed columns, sparse columns, and default constraints.

SELECT s.name  AS SchemaName,
       t.name  AS TableName,
       c.name  AS ColumnName,
       ty.name AS DataType,
       c.max_length AS MaxLength,
       c.precision,
       c.scale,
       c.is_nullable,
       c.is_identity
FROM sys.columns c
JOIN sys.objects t
  ON c.object_id = t.object_id
JOIN sys.schemas s
  ON t.schema_id = s.schema_id
JOIN sys.types ty
  ON c.user_type_id = ty.user_type_id
WHERE c.name LIKE '%email%'
  AND t.type IN ('U','V')   -- U = user table, V = view
ORDER BY SchemaName, TableName, c.column_id;

Why professionals prefer this method:

  • You can filter by is_identity, is_computed, or is_sparse.
  • You can join to sys.extended_properties to search column descriptions.
  • You can sort by column_id to preserve the physical order of fields.

A shorter alternative when you only need the table name:

SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName
FROM sys.columns
WHERE name LIKE '%orderid%';

Method 3: Search by Data Type or Attribute

Sometimes you know the shape of the field but not its name. The following query finds every column that could hold a national identification number:

SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS DataType
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('char','varchar','nchar','nvarchar')
  AND c.max_length BETWEEN 9 AND 20;

You can adapt the same pattern to find all datetime2 columns, all decimal(18,2) money fields, or every nullable column in a schema Simple, but easy to overlook. Turns out it matters..

Method 4: Search Across All Databases on the Instance

Metadata views only see the current database

The INFORMATION_SCHEMA and sys views are scoped to the current database. Because of that, to search across all databases on a SQL Server instance, you must dynamically construct and execute queries for each database. On the flip side, below is a template using a cursor or a set-based approach with sp_MSforeachdb (an undocumented but widely used stored procedure). Exercise caution with sp_MSforeachdb in production, as it is not officially supported and may behave unexpectedly in edge cases That's the part that actually makes a difference..

Set-Based Approach with sp_MSforeachdb

EXEC sp_MSforeachdb '
USE [?];
SELECT 
    DB_NAME() AS DatabaseName,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ''%email%''
ORDER BY TABLE_SCHEMA, TABLE_NAME;
';

Dynamic SQL with a Cursor (More Control)

DECLARE @db_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE state = 0;  -- Online databases only

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
    USE [' + @db_name + N'];
    SELECT 
        ''' + @db_name + N''' AS DatabaseName,
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME,
        DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME LIKE ''%email%''
    ORDER BY TABLE_SCHEMA, TABLE_NAME;';

    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @db_name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

Considerations for Cross-Database Searches:

  • Always exclude system databases (e.g., master, tempdb) unless intentionally auditing them.
  • Use sys.databases to filter for user databases or specific compatibility levels.
  • For large instances, limit the search to relevant databases to avoid performance overhead.

Method 5: Using SQL Server Management Studio (SSMS) GUI

For those who prefer a graphical interface, SSMS offers a built-in search:

  1. In Object Explorer, right-click a database and select Search.
  2. In real terms, choose Find and enter your column name or pattern. 3. Adjust filters for schemas, object types, or data types.

This method is intuitive but less scriptable and may not scale well for complex patterns or frequent use.

Best Practices and Pitfalls

  • Case Sensitivity: Always verify the database collation. If case-sensitive, use COLLATE clauses or adjust patterns accordingly.
  • Performance: On large databases, avoid LIKE '%pattern%' with leading wildcards, as it prevents index usage. Consider full-text search for extensive text searches.
  • Security: Ensure you have appropriate permissions to view metadata. In some environments, you may need to restrict access to sensitive columns (e.g., PII) using row-level security or dynamic data masking.
  • Documentation: Extend your queries to include extended properties or comments for better documentation. For example:
    SELECT 
        c.name AS ColumnName,
        ep.value AS Description
    FROM sys.columns c
    LEFT JOIN sys.extended_properties ep 
        ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
    WHERE c.name LIKE '%email%';
    

Conclusion

Mastering metadata queries empowers you to handle complex databases efficiently. Whether you use the standard INFORMATION_SCHEMA views for portability, the detailed sys catalog views for deep insights, or dynamic SQL for cross-database searches, these techniques are indispensable for database administrators and developers. Think about it: always tailor your approach to the specific requirements of your environment, and remember that combining multiple methods—such as filtering by data type and searching extended properties—can yield the most comprehensive results. By integrating these strategies into your workflow, you’ll spend less time hunting for columns and more time delivering value through data-driven solutions Not complicated — just consistent..

New and Fresh

What's New

You'll Probably Like These

Cut from the Same Cloth

Thank you for reading about Search For Field Name In Sql Server. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home