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

搜索表：

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;

搜索表和视图：

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

---

SQL 服务器：

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

甲骨文：

SELECT owner, table_name, column_name 
FROM all_tab_columns 
WHERE column_name LIKE '%YOUR_COLUMN_NAME%'
AND OWNER IN ('YOUR_SCHEMA_NAME');

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

---

这应该有效：

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

---

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%'

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

and c.is_nullable = 0

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

享受。

---

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

ApexSQL 搜索

SSMS 工具包

红门工具。

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

---

我不知道为什么你们中的许多人建议加入 sys.table with sys.columns。

您可以使用以下代码：

SELECT Object_name(object_id) AS TableName,
       *
FROM   sys.columns
WHERE  NAME LIKE '%MyName%' 

或者

如果您还想要架构名称：

SELECT *
FROM   information_schema.columns
WHERE  column_name LIKE '%MyName%' 

---

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

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

如果你也想要模式名称（在很多情况下你会这样做，因为你会有很多不同的模式，除非你能记住数据库中的每个表及其所属的位置，否则这可能很有用）运行：

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

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

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

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

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

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

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

此版本允许您将其保存在管理数据库中，然后搜索任何数据库。将 @db 的声明从 '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

---

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

---

这是您问题的答案

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%';

---

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; 

它来自 Pinal Sir 博客

---

您可以通过 column_name 过滤器从 INFORMATION_SCHEMA.COLUMNS 中找到它

Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
     From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'

---

以下查询将为您提供具有字段名称（如“%myName”）的数据库的确切表名。

SELECT distinct(TABLE_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'

---

获取完整信息：列名、表名以及表的架构。

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

---

SELECT  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;

---

在 MS SQL Server 数据库中，使用此查询来获取包含输入文本的表和相应的列名：

SELECT t.name AS tableName, c.name AS columnName 
FROM sys.tables as t 
INNER JOIN sys.columns AS c ON t.object_id=c.object_id 
WHERE c.name LIKE '%<your_search_string>%'

---

SELECT table_schema + '.' + table_name,
       column_name
FROM   [yourdatabase].information_schema.columns
WHERE  column_name LIKE '%myName%' 

这将为您提供您需要查找的列的表名。

---

我想要一些不会让我眼睛流血的桌子和视图。

询问

SELECT
    t.TABLE_TYPE AS [Type],
    c.TABLE_NAME AS [Object],
    c.COLUMN_NAME AS [Column]
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
    LEFT JOIN INFORMATION_SCHEMA.TABLES AS t ON
        t.TABLE_CATALOG = c.TABLE_CATALOG AND 
        t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
        t.TABLE_NAME = c.TABLE_NAME
WHERE
    c.COLUMN_NAME LIKE '%myName%'
ORDER BY
    [Type],
    [Object],
    [Column]

结果

Type        Object   Column
----------------------------
BASE TABLE  Table1   myName1
BASE TABLE  Table2   myName2
VIEW        View1    myName1
VIEW        View2    myName2

---

我刚刚尝试过，效果很好

USE YourDatabseName
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 '%YourColumnName%'
ORDER BY schema_name, table_name;

只需将 YourDatbaseName 更改为您的数据库，将 YourcolumnName 更改为您要查找的列名，其余的保持原样。

希望这有帮助

---

这个简单的查询对我有用。

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'schemaName'
AND column_name like '%model%';

---

DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'

SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name

---

我将它用于相同的目的并且它有效：

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'

---

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 '%Label%'
ORDER BY schema_name, table_name;

---

像 oracle 一样，您可以通过以下方式找到表和列：

select table_name, column_name
from user_tab_columns 
where column_name 
like '%myname%';

---

你可以试试这个查询：

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 '%myName%'

---

希望这不是一个重复的答案，但我喜欢做的是在 sql 语句中生成一个 sql 语句，这将允许我搜索我正在寻找的值（不仅仅是具有这些字段名称的表（因为它通常是然后我有必要删除与我要查找的列名的 id 相关的任何信息）：

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

然后我可以复制并粘贴运行我的第一列“SQLToRun”...然后我将“Select * from”替换为“Delete from”，它允许我删除对该给定 ID 的任何引用！将这些结果写入文件，以便您以防万一。

注意**** 确保在运行您的删除语句之前消除任何 bakup 表...

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

---

只是为了改进上面的答案，我还包括了视图，并将架构和表/视图连接在一起，使结果更加明显。

DECLARE @COLUMNNAME AS VARCHAR(100);

SET @COLUMNNAME = '%Absence%';

SELECT CASE
           WHEN [T].[NAME] IS NULL
           THEN 'View'
           WHEN [T].[NAME] = ''
           THEN 'View'
           ELSE 'Table'
       END AS [TYPE], '[' + [S].[NAME] + '].' + '[' + CASE
                                                          WHEN [T].[NAME] IS NULL
                                                          THEN [V].[NAME]
                                                          WHEN [T].[NAME] = ''
                                                          THEN [V].[NAME]
                                                          ELSE [T].[NAME]
                                                      END + ']' AS [TABLE], [C].[NAME] AS [COLUMN]
FROM [SYS].[SCHEMAS] AS [S] LEFT JOIN [SYS].[TABLES] AS [T] ON [S].SCHEMA_ID = [T].SCHEMA_ID
                            LEFT JOIN [SYS].[VIEWS] AS [V] ON [S].SCHEMA_ID = [V].SCHEMA_ID
                            INNER JOIN [SYS].[COLUMNS] AS [C] ON [T].OBJECT_ID = [C].OBJECT_ID
                                                                 OR
                                                                 [V].OBJECT_ID = [C].OBJECT_ID
                            INNER JOIN [SYS].[TYPES] AS [TY] ON [C].[SYSTEM_TYPE_ID] = [TY].[SYSTEM_TYPE_ID]
WHERE [C].[NAME] LIKE @COLUMNNAME
GROUP BY '[' + [S].[NAME] + '].' + '[' + CASE
                                             WHEN [T].[NAME] IS NULL
                                             THEN [V].[NAME]
                                             WHEN [T].[NAME] = ''
                                             THEN [V].[NAME]
                                             ELSE [T].[NAME]
                                         END + ']', [T].[NAME], [C].[NAME], [S].[NAME]
ORDER BY '[' + [S].[NAME] + '].' + '[' + CASE
                                             WHEN [T].[NAME] IS NULL
                                             THEN [V].[NAME]
                                             WHEN [T].[NAME] = ''
                                             THEN [V].[NAME]
                                             ELSE [T].[NAME]
                                         END + ']', CASE
                                                        WHEN [T].[NAME] IS NULL
                                                        THEN 'View'
                                                        WHEN [T].[NAME] = ''
                                                        THEN 'View'
                                                        ELSE 'Table'
                                                    END, [T].[NAME], [C].[NAME];

---

Create table #yourcolumndetails(
DBaseName varchar(100), 
TableSchema varchar(50), 
TableName varchar(100),
ColumnName varchar(100), 
DataType varchar(100), 
CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''origin'''

select * from #yourcolumndetails
Drop table #yourcolumndetails

---

用于显示所有具有指定列名的表的 SQL 查询：

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
  FROM sys.tables t
 INNER JOIN sys.columns c ON c.object_id = t.object_id
 WHERE c.name like '%ColumnName%'
 ORDER BY 'Table Name'

---

在 MS SQL 中，您可以编写以下行来检查特定表的列名：

sp_help your_tablename

或者，您可以先在查询窗口中选择您的表名（突出显示架构和表名），然后按下面的组合键：

Alt + F1

---