Working with massive legacy databases in SQL Server Management Studio 2008 can be particularly challenging when you need to locate specific columns. With potentially thousands of tables and columns, manually searching through Object Explorer simply isn't practical.

The most efficient way to find columns containing specific substrings is by querying SQL Server's system catalog views. Here's the fundamental query structure:

SELECT 
    t.name AS TableName,
    c.name AS ColumnName
FROM 
    sys.tables t
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
WHERE 
    c.name LIKE '%YourSubstring%'
ORDER BY 
    t.name, c.name;

For a more comprehensive search that includes views and other objects:

SELECT 
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    c.name AS ColumnName
FROM 
    sys.objects o
INNER JOIN 
    sys.columns c ON o.object_id = c.object_id
WHERE 
    c.name LIKE '%search%'
    AND o.type IN ('U', 'V') -- U = User table, V = View
ORDER BY 
    o.type_desc, o.name, c.name;

To search all databases on your server instance:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
UNION ALL
SELECT 
    ''' + name + ''' AS DatabaseName,
    t.name AS TableName,
    c.name AS ColumnName
FROM 
    ' + QUOTENAME(name) + '.sys.tables t
INNER JOIN 
    ' + QUOTENAME(name) + '.sys.columns c 
    ON t.object_id = c.object_id
WHERE 
    c.name LIKE ''%YourSubstring%'' COLLATE SQL_Latin1_General_CP1_CI_AS'
FROM sys.databases 
WHERE state = 0; -- Only online databases

SET @sql = STUFF(@sql, 1, 10, '') + ' ORDER BY DatabaseName, TableName, ColumnName';
EXEC sp_executesql @sql;

For frequent use, consider creating a stored procedure:

CREATE PROCEDURE FindColumnsBySubstring
    @Substring NVARCHAR(128),
    @SchemaName NVARCHAR(128) = NULL,
    @TableName NVARCHAR(128) = NULL
AS
BEGIN
    SELECT 
        s.name AS SchemaName,
        t.name AS TableName,
        c.name AS ColumnName,
        ty.name AS DataType,
        c.max_length,
        c.precision,
        c.scale,
        c.is_nullable
    FROM 
        sys.tables t
    INNER JOIN 
        sys.columns c ON t.object_id = c.object_id
    INNER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN
        sys.types ty ON c.user_type_id = ty.user_type_id
    WHERE 
        c.name LIKE '%' + @Substring + '%'
        AND (@SchemaName IS NULL OR s.name = @SchemaName)
        AND (@TableName IS NULL OR t.name = @TableName)
    ORDER BY 
        s.name, t.name, c.name;
END

Finding all columns containing "date" in their names:

EXEC FindColumnsBySubstring @Substring = 'date';

Searching for "price" columns in the "Sales" schema:

EXEC FindColumnsBySubstring 
    @Substring = 'price', 
    @SchemaName = 'Sales';

Working with legacy SQL Server databases often means dealing with thousands of tables and columns with obscure naming conventions. Finding specific columns becomes like searching for a needle in a haystack, especially when you only remember part of the column name.

SQL Server provides several system catalog views that store metadata about database objects. The most useful ones for column searches are:

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length,
    c.precision,
    c.scale
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE c.name LIKE '%YourSearchTerm%'
ORDER BY t.name, c.name;

For frequent use, consider creating a stored procedure:

CREATE PROCEDURE usp_FindColumnsByName
    @SearchTerm NVARCHAR(128)
AS
BEGIN
    SELECT 
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        t.name AS TableName,
        c.name AS ColumnName,
        ty.name AS DataType,
        c.max_length,
        c.precision,
        c.scale,
        CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS PrimaryKey
    FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
    LEFT JOIN (
        SELECT ic.object_id, ic.column_id
        FROM sys.indexes i
        INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        WHERE i.is_primary_key = 1
    ) pk ON t.object_id = pk.object_id AND c.column_id = pk.column_id
    WHERE c.name LIKE '%' + @SearchTerm + '%'
    ORDER BY SchemaName, TableName, ColumnName;
END;

For enterprise environments, you might need to search across multiple databases:

DECLARE @SearchTerm NVARCHAR(128) = 'addr';
DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = COALESCE(@SQL + CHAR(13) + CHAR(10) + 'UNION ALL' + CHAR(13) + CHAR(10), '') +
'SELECT ''' + name + ''' AS DatabaseName, 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName, 
    c.name AS ColumnName
FROM ' + QUOTENAME(name) + '.sys.tables t
INNER JOIN ' + QUOTENAME(name) + '.sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE ''%' + @SearchTerm + '%'''
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

EXEC sp_executesql @SQL;

For standard compliance, you can use INFORMATION_SCHEMA views:

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%date%'
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;