Find all tables containing column with specified name – MS SQL Server – Dev

The best answers to the question “Find all tables containing column with specified name – MS SQL Server” in the category Dev.

QUESTION:

Is it possible to query for table names which contain columns being

LIKE '%myName%'

?

ANSWER:

We can also use the following syntax:-

select * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%clientid%' 
order by TABLE_NAME

ANSWER:

Search Tables:

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

Search Tables and Views:

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

ANSWER:

select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like '%ColumnName%'

This here will give you a little extra information about the schema, tables and columns that you may or may not choose to use extra conditions in your where clause to filter on. For example, if you only wanted to see the fields which must have values add

and c.is_nullable = 0

You could add other conditionals, I also added the columns in the select clause in this vertical manner so it was easy to reorder, remove, rename, or add others based on your needs. Alternately you could search for just tables by using T.Name. Its very customisable.

Enjoy.

ANSWER:

SQL Server:

SELECT Table_Name, Column_Name 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YOUR_DATABASE'
AND COLUMN_NAME LIKE '%YOUR_COLUMN%'

Oracle:

SELECT owner, table_name, column_name 
FROM all_tab_columns 
WHERE column_name LIKE '%YOUR_COLUMN_NAME%'
AND OWNER IN ('YOUR_SCHEMA_NAME');
  • SIMPLE AS THAT!! (SQL, PL/SQL)
    I use it ALL the time to find ALL instances of a column name in a given database (schema).