Advertisement
Advertisement


Find all tables containing column with specified name - MS SQL Server


Question

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

LIKE '%myName%'

?

2018/04/25
1
1248
4/25/2018 1:48:15 PM


We can also use the following syntax:-

select * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%clientid%' 
order by TABLE_NAME
2014/10/02

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).
2019/03/14

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.

2019/05/24

This should work:

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )
2014/10/02

If you’re more into third party tools there a lot of options there such as:

These come in very handy if your database contains encrypted objects (views, procedures, functions) because you can’t easily search for these using system tables.

2016/11/16

Source: https://stackoverflow.com/questions/4849652
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]