查找包含具有指定名称的列的所有表-MSSQLServer

这应该工作：

SELECT nameFROM sysobjectsWHERE id IN ( SELECT idFROM syscolumnsWHERE name like '%column_name%' )

搜索表格：

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

搜索表和视图：

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

SELECT COLUMN_NAME, TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME LIKE '%myName%'

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

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

USE AdventureWorks
GO
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%EmployeeID%'ORDER BY schema_name, table_name;

文章来源：Pina Sir Blog

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

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

selects.[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 sinner join  sys.tables  ton s.schema_id = t.schema_idinner join  sys.columns con t.object_id = c.object_idinner join  sys.types   don c.user_type_id = d.user_type_idwhere c.name like '%ColumnName%'

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

and c.is_nullable = 0

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

好好享受。

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

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

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

SELECT  [TABLE_NAME] ,[INFORMATION_SCHEMA].COLUMNS.COLUMN_NAMEFROM    INFORMATION_SCHEMA.COLUMNSWHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;

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 LengthFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDINNER JOIN sys.types ty ON c.system_type_id = ty.system_type_idWHERE c.name LIKE @columnNameORDER BY t.name, c.name

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

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRunFROM sys.columns c, c.name as ColName, t.name as TableNameJOIN sys.tables tON c.object_id = t.object_idWHERE c.name LIKE '%ProjectID%'

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

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

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRunFROM sys.columns c, c.name as ColName, t.name as TableNameJOIN sys.tables tON c.object_id = t.object_idWHERE c.name LIKE '%ProjectID%'

我刚刚试过了，这是完美的

USE YourDatabseNameGOSELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%YourColumnName%'ORDER BY schema_name, table_name;

仅将YourDatbase Name更改为您的数据库，将YourbronName更改为您正在查找的列名，其余部分保持原样。

希望这有帮助

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

您可以使用以下代码：

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

或

如果您还想要模式名称：

SELECT *FROM   information_schema.columnsWHERE  column_name LIKE '%MyName%'

我用它来达到同样的目的，它奏效了：

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

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

DECLARE @COLUMNNAME AS VARCHAR(100);
SET @COLUMNNAME = '%Absence%';
SELECT CASEWHEN [T].[NAME] IS NULLTHEN 'View'WHEN [T].[NAME] = ''THEN 'View'ELSE 'Table'END AS [TYPE], '[' + [S].[NAME] + '].' + '[' + CASEWHEN [T].[NAME] IS NULLTHEN [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_IDLEFT JOIN [SYS].[VIEWS] AS [V] ON [S].SCHEMA_ID = [V].SCHEMA_IDINNER JOIN [SYS].[COLUMNS] AS [C] ON [T].OBJECT_ID = [C].OBJECT_IDOR[V].OBJECT_ID = [C].OBJECT_IDINNER JOIN [SYS].[TYPES] AS [TY] ON [C].[SYSTEM_TYPE_ID] = [TY].[SYSTEM_TYPE_ID]WHERE [C].[NAME] LIKE @COLUMNNAMEGROUP BY '[' + [S].[NAME] + '].' + '[' + CASEWHEN [T].[NAME] IS NULLTHEN [V].[NAME]WHEN [T].[NAME] = ''THEN [V].[NAME]ELSE [T].[NAME]END + ']', [T].[NAME], [C].[NAME], [S].[NAME]ORDER BY '[' + [S].[NAME] + '].' + '[' + CASEWHEN [T].[NAME] IS NULLTHEN [V].[NAME]WHEN [T].[NAME] = ''THEN [V].[NAME]ELSE [T].[NAME]END + ']', CASEWHEN [T].[NAME] IS NULLTHEN 'View'WHEN [T].[NAME] = ''THEN 'View'ELSE 'Table'END, [T].[NAME], [C].[NAME];

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%Label%'ORDER BY schema_name, table_name;

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

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

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

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

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

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columnswhere 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)ASSELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columnsWHERE name LIKE CONCAT('%',@name,'%')ORDER BY [Table Name] ASC, [Column] ASCGO

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

自从我发布这篇文章后，我重新格式化了这个过程。它现在有点高级，但看起来更混乱（但它在一个过程中，所以你永远不会看到它），它的格式更好。

此版本允许您将其放在管理数据库中，然后搜索任何数据库。将@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'ASDECLARE @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 colLEFT JOIN ',@db,'.sys.objects obON ob.object_id = col.object_idWHEREcol.name LIKE CONCAT(''%'',''',@name,''',''%'')AND ob.type =''U''ORDER BY [Table Name] ASC,[Column] ASC')EXECUTE (@sql)GO

SQL服务器：

SELECT Table_Name, Column_NameFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_CATALOG = 'YOUR_DATABASE'AND COLUMN_NAME LIKE '%YOUR_COLUMN%'

甲骨文：

SELECT owner, table_name, column_nameFROM all_tab_columnsWHERE column_name LIKE '%YOUR_COLUMN_NAME%'AND OWNER IN ('YOUR_SCHEMA_NAME');

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

像oracle一样，你可以用这个找到表和列：

select table_name, column_namefrom user_tab_columnswhere column_namelike '%myname%';

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

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

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 SELECTTable_Catalog,Table_Schema,Table_Name,Column_Name,Data_Type,Character_Maximum_LengthFROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME like ''origin'''
select * from #yourcolumndetailsDrop table #yourcolumndetails

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

SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMAFROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME LIKE '%col_Name%'

您可以尝试此查询：

USE AdventureWorksGOSELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%myName%'

这是赛贝斯数据库的工作解决方案

selectt.table_name,c.column_namefromsystab as t key join systabcol as cwherec.column_name = 'MyColumnName'

我们可以使用sp_columns作为目的。

sp_columns 'table name', null, null, '%column name%'

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

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

我想要一些表和视图的东西，不会让我的眼睛流血。

查询

SELECTt.TABLE_TYPE AS [Type],c.TABLE_NAME AS [Object],c.COLUMN_NAME AS [Column]FROMINFORMATION_SCHEMA.COLUMNS AS cLEFT JOIN INFORMATION_SCHEMA.TABLES AS t ONt.TABLE_CATALOG = c.TABLE_CATALOG ANDt.TABLE_SCHEMA = c.TABLE_SCHEMA ANDt.TABLE_NAME = c.TABLE_NAMEWHEREc.COLUMN_NAME LIKE '%myName%'ORDER BY[Type],[Object],[Column]

结果

Type        Object   Column----------------------------BASE TABLE  Table1   myName1BASE TABLE  Table2   myName2VIEW        View1    myName1VIEW        View2    myName2

这是你问题的答案

SELECT c.name AS ColumnName, t.name AS TableNameFROM sys.columns cJOIN sys.tables t ON c.object_id = t.object_idWHERE c.name LIKE '%myName%';

在MSSQLServer Database中，使用此查询获取包含输入文本的表和相应的列名：

SELECT t.name AS tableName, c.name AS columnNameFROM sys.tables as tINNER JOIN sys.columns AS c ON t.object_id=c.object_idWHERE c.name LIKE '%<your_search_string>%'

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

sp_help your_tablename

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

Alt + F1

此外，您还可以找到具有指定模式的列名。

SELECT 'DBName' AS DBName,column_name,table_name,table_schemaFROM   dbname.information_schema.columnsWHERE  column_name LIKE '%YourColumnName%'AND table_schema IN ( 'YourSchemaName' )

您还可以在多个数据库中找到相同的列。

SELECT 'DBName1' AS DB,column_name,table_name,table_schemaFROM   dbname1.information_schema.columnsWHERE  column_name LIKE '%YourColumnName%'UNIONSELECT 'DBName2' AS DB,column_name,table_name,table_schemaFROM   dbname2.information_schema.columnsWHERE  column_name LIKE '%YourColumnName%'

SELECT table_schema + '.' + table_name,column_nameFROM   [yourdatabase].information_schema.columnsWHERE  column_name LIKE '%myName%'

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

这个简单的查询对我很有效。

SELECT TABLE_NAME, COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE table_schema = 'schemaName'AND column_name like '%model%';

您可以在SQLServer中使用下面的查询。

SELECT column_name AS 'ColumnName',table_name  AS 'TableName'FROM   information_schema.columnsWHERE  column_name LIKE '%columnname%'AND table_schema = 'schema'ORDER  BY tablename,columnname;

它将返回table_nameschema_namecolumn_name

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns cON t.object_id = c.object_idWHERE c.name LIKE '%colName%'ORDER BY schema_name,table_name;