Find all tables containing column with specified name

 

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

LIKE '%myName%'

?

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 & Views: 

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

我们也可以使用以下语法： - 

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

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

  • ApexSQL Search
  • SSMS Toolpack
  • Red Gate tools.

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

    链接地址: http://www.djcxy.com/p/7658.html

    上一篇: 如何使用SQL Server删除INNER JOIN？

    下一篇: 查找包含具有指定名称的列的所有表