查找包含具有指定名称的列的所有表 - MS SQL Server

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

提问by gruber

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

是否可以查询包含列的表名

LIKE '%myName%'

?

?

回答by AdaTheDev

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;

回答by Khwaza Bandenawaz

We can also use the following syntax:-

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

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

回答by Todd-ECU

SQL Server:

SQL 服务器：

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

• 就那么简单！！（SQL、PL/SQL）
  我一直使用它来查找给定数据库（架构）中列名的所有实例。

回答by Francis Rodgers

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

这将为您提供有关架构、表和列的一些额外信息，您可能会或可能不会选择在 where 子句中使用额外条件进行过滤。例如，如果您只想查看必须添加值的字段

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.

您可以添加其他条件，我还以这种垂直方式在 select 子句中添加了列，因此很容易根据您的需要重新排序、删除、重命名或添加其他条件。或者，您可以使用 T.Name 仅搜索表。它非常可定制。

Enjoy.

享受。

回答by cichy

This should work:

这应该有效：

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )

回答by Dwoolk

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

如果您更喜欢第三方工具，则有很多选择，例如：

• ApexSQL Search
• SSMS Toolpack
• Red Gate tools.

• ApexSQL 搜索
• SSMS 工具包
• 红门工具。

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.

如果您的数据库包含加密对象（视图、过程、函数），这些将非常方便，因为您无法使用系统表轻松搜索这些对象。

回答by user3583912

I don't know why so many of you suggesting Joining with sys.table with sys.columnsyou can simply use below code:

我不知道为什么这么多人建议加入sys.table with sys.columns你可以简单地使用以下代码：

Select object_name(object_id) as TableName,* from SYS.columns where name LIKE '%MyName%'

Select object_name(object_id) as TableName,* from SYS.columns where name LIKE '%MyName%'

or

或者

If you want schema name as well:

如果您还需要架构名称：

Select * from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME LIKE '%MyName%'

回答by Ste Bov

If you simply want the table name you can run:

如果你只是想要表名，你可以运行：

select object_name(object_id) from sys.columns
where name like '%received_at%'

If you want the Schema Name as well (which in a lot of cases you will, as you'll have a lot of different schemas, and unless you can remember every table in the database and where it belongs this can be useful) run:

如果您还需要架构名称（在很多情况下您会这样做，因为您将拥有许多不同的架构，除非您能记住数据库中的每个表及其所属的位置，否则这可能很有用）运行：

select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columns
where name like '%received_at%'

and finally if you want it in a nicer format (although this is where the code (In my opinion) is getting too complicated for easy writing):

最后，如果你想要更好的格式（尽管这是代码（在我看来）变得太复杂而无法轻松编写的地方）：

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns
where name like '%received_at%'

note you can also create a function based on what I have:

请注意，您还可以根据我拥有的内容创建一个函数：

CREATE PROCEDURE usp_tablecheck
--Scan through all tables to identify all tables with columns that have the provided string
--Stephen B
@name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

It is worth noting that the concat feature was added in 2012. For 2008r2 and earlier use + to concatenate strings.

值得注意的是 concat 功能是在 2012 年添加的。对于 2008r2 及更早版本，使用 + 来连接字符串。

I've re-formatted the proc a bit since I posted this. It's a bit more advanced now but looks a lot messier (but it's in a proc so you'll never see it) and it's formatted better.

自从我发布这个后，我已经重新格式化了 proc。它现在更先进一些，但看起来更混乱（但它在一个过程中，所以你永远不会看到它）并且它的格式更好。

This version allows you to have it in an administrative database and then search through any database. Change the decleration of @dbfrom 'master'to whichever you want the default database to be (NOTE: using the CONCAT() function will only work with 2012+ unless you change the string concatenation to use the +operators).

此版本允许您将其放在管理数据库中，然后搜索任何数据库。将@dbfrom的 decleration 更改为'master'您想要的默认数据库（注意：使用 CONCAT() 函数仅适用于 2012+，除非您更改字符串连接以使用+运算符）。

CREATE PROCEDURE [dbo].[usp_tablecheck]
    --Scan through all tables to identify all tables in the specified database with columns that have the provided string
    --Stephen B
    @name nvarchar(200)
    ,@db nvarchar(200) = 'master'
AS
    DECLARE @sql nvarchar(4000) = CONCAT('
        SELECT concat(OBJECT_SCHEMA_NAME(col.object_id,DB_ID(''',@db,''')),''.'',object_name(col.object_id,DB_ID(''',@db,'''))) AS [Table Name]
            ,col.name AS [Column] 
        FROM ',@db,'.sys.columns col
        LEFT JOIN ',@db,'.sys.objects ob 
            ON ob.object_id = col.object_id
        WHERE 
            col.name LIKE CONCAT(''%'',''',@name,''',''%'') 
            AND ob.type =''U''
        ORDER BY [Table Name] ASC
            ,[Column] ASC')
    EXECUTE (@sql)
GO

回答by shadab shah

USE AdventureWorks

GO

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name; 

It is from Pinal Sir Blog

它来自 Pinal Sir 博客

回答by Neil Knight

SELECT COLUMN_NAME, TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'