SQL Server search for a column by name

user_444443 0
Viewed 42667

I'm doing some recon work and having to dig through a few hundred SQL Server database tables to find columns.

Is there a way to easily search for columns in the database and return just the table name that the column belongs to?

I found this, but that also returns Stored procedures with that column name in it...

sql-server-2005 sql-server sql
6 Answers

This stored procedure will search for table.name and column.name pairs.

I use when I have "WhateverId" in code and I want to know where that is (probably) stored in the database without actually having to read through and understand the code. :)

CREATE OR ALTER PROC FindColumns  
@ColumnName VARCHAR(MAX) = NULL,  
@TableName VARCHAR(MAX) = NULL    
AS    
    
SELECT T.[name] AS TableName, C.[name] AS ColumnName  
FROM sys.all_columns C    
JOIN sys.tables T ON C.object_id = T.object_id    
JOIN sys.types CT ON C.user_type_id = CT.user_type_id    
WHERE (@ColumnName IS NULL OR C.[name] LIKE '%' + TRIM(@ColumnName) + '%')  
AND (@TableName IS NULL OR T.[name] LIKE '%' + TRIM(@TableName) + '%')  
ORDER BY T.[name], C.[name]
user_9538020
Related