SQL Server search for a column by name

Question

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...

I created a [procedure](https://stackoverflow.com/a/45681429/4271117) to search procedures, tables, views, or jobs. You can specify to search only one of them or all of them. The link points to an answer at stack overflow — Weihui Guo, May 24 '18 at 12:37

score 44 · Accepted Answer · answered Sep 21 '10 at 15:10

SELECT OBJECT_NAME(object_id) FROM sys.columns WHERE name = 'foo'

This includes views though but can be further filtered . It may be useful though.

More generally...

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'foo'

sys.columns

score 9 · Answer 2 · answered Sep 21 '10 at 15:30

To get the

1) full column name
2) object name (including schema)
3) object type (table/view)
4) data type (nice format: varchar(6) or numeric(5,2), etc.)
5) null/not null
6) information on identity, check constraint, and default info

try this:

DECLARE @Search varchar(200)
SET @Search='YourColumnName'  --can be a partial or a complete name

SELECT
    s.name as ColumnName
        ,sh.name+'.'+o.name AS ObjectName
        ,o.type_desc AS ObjectType
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
            WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name
         END AS DataType

        ,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL'
        END AS Nullable
        ,CASE
             WHEN ic.column_id IS NULL THEN ''
             ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
         END
        +CASE
             WHEN sc.column_id IS NULL THEN ''
             ELSE ' computed('+ISNULL(sc.definition,'')+')'
         END
        +CASE
             WHEN cc.object_id IS NULL THEN ''
             ELSE ' check('+ISNULL(cc.definition,'')+')'
         END
            AS MiscInfo
    FROM sys.columns                           s
        INNER JOIN sys.types                   t ON s.system_type_id=t.system_type_id and t.is_user_defined=0
        INNER JOIN sys.objects                 o ON s.object_id=o.object_id
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    WHERE s.name LIKE '%'+@Search+'%'

This is beautiful. Thank you @KM. – Valor Jul 06 '23 at 19:45 — Valor, Jul 06 '23 at 19:45

score 5 · Answer 3 · answered Sep 21 '10 at 15:12

5

select c.name as ColumnName, o.name as TableName
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where c.name = 'MyColumnName'

answered Sep 21 '10 at 15:12

D'Arcy Rittich

167,292
40
290
283

score 1 · Answer 4 · answered Sep 21 '10 at 15:10

1

http://www.red-gate.com/products/SQL_Search/index.htm?utm_source=google&utm_medium=cpc&utm_content=brand_aware&utm_campaign=sqlsearch&gclid=COSfqe_mmKQCFSE1gwodSxOrEQ

This should do it for you. I use it everyday.

answered Sep 21 '10 at 15:10

Scott

2,143
2
19
25

score 1 · Answer 5 · answered Dec 30 '20 at 23:17

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]

Kilanash · Answer 6 · 2010-09-21T15:25:28.633

0

select table_name from information_schema.columns
where column_name = '<your column name here>'

Using the information_schema views is 'more correct' as system details in the system databases are subject to change between implementations of SQL Server.

edited Sep 21 '10 at 15:25

answered Sep 21 '10 at 15:13

Kilanash

4,479
1
14
11

Why would you have DISTINCT when column names must be unique in a table? – gbn Sep 21 '10 at 15:15
I was under the strange impression that the table name could show up multiple times (especially if you have multiple databases with similar schemas, and don't restrict your query to a specific schema or database). In hindsight the distinct is probably useless in most contexts. – Kilanash Sep 21 '10 at 15:25

SQL Server search for a column by name

6 Answers6

Linked

Related