Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server search for a column by name

Tags:

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

like image 885
EJC Avatar asked Sep 21 '10 15:09

EJC


People also ask

Can you search for a column name in SQL?

A feature that can be used to search for column names in SQL Server is Object search.

How do I search for a specific column in SQL?

SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME LIKE 'PREFIX%' ORDER BY TABLE_NAME; Query: SELECT * FROM INFORMATION_SCHEMA.

How do I query column names in SQL Server?

USE db_name; DESCRIBE table_name; it'll give you column names with the type.

How do I select only column names in SQL?

To get the column name of a table we use sp_help with the name of the object or table name. sp_columns returns all the column names of the object. The following query will return the table's column names: sp_columns @table_name = 'News'


2 Answers

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

like image 150
gbn Avatar answered Sep 30 '22 02:09

gbn


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+'%'
like image 42
KM. Avatar answered Sep 30 '22 02:09

KM.