提问



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


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

其它参考1


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


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

其它参考2


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



  • 简直就是这样!! (SQL,PL/SQL)
    我一直用它来查找给定数据库(模式)中列名的所有实例。


其它参考3


如果您更多地使用第三方工具,那里有很多选项,例如:



  • ApexSQL搜索

  • SSMS Toolpack

  • 红门工具。



如果您的数据库包含加密对象(视图,过程,函数),这些非常方便,因为您无法使用系统表轻松搜索这些对象。 [43] [44] [45]

其它参考4


这应该工作:


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

其它参考5


select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        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搜索表。它非常可定制。


请享用。

其它参考6


我不知道为什么这么多人建议加入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%'

其它参考7


如果您只想要表名,则可以运行:


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


如果你也想要Schema Name(在很多情况下,你将会有很多不同的模式,除非你能记住数据库中的每个表以及它所属的位置,这可能很有用)运行:


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


值得注意的是,2012年添加了concat功能。对于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

其它参考8


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 Blog

其它参考9


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

其它参考10


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


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

其它参考11


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

其它参考12


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


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

其它参考13


获取完整信息:列名,表名以及表的架构。


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

其它参考14


我刚尝试过,这完美无缺


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更改为您要查找的列名,并保持原样。


希望这有所帮助

其它参考15


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

其它参考16


我用它来达到同样的目的,它起作用了:


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

其它参考17


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


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

其它参考18


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;

其它参考19


像oracle一样,您可以使用以下命令查找表和列:


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

其它参考20


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


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];

其它参考21


您可以使用[INFORMATION_SCHEMA].[COLUMNS]表来查找列



Select * From [INFORMATION_SCHEMA].[COLUMNS] Where COLUMN_NAME like '%Column%'


获取给定SQL语句访问的表和列信息
http://www.w3hattrick.com/2016/05/getting-table-and-column-information.html[46]

其它参考22


您可以尝试以下查询:


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

其它参考23


对于Oracle,具有普通用户权限:


select owner, table_name, column_name
from all_tab_columns 
where column_name 
like '%myname%';

其它参考24


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


有用

其它参考25


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

其它参考26


SELECT col.Name AS ColumnName, tab.Name AS TableName 
FROM sys.columns col  
     JOIN sys.tables tab   
ON col.Object_id = tab.Object_id   
WHERE col.Name LIKE '%MyName%'

其它参考27


这是Sybase数据库的有效解决方案


select 
  t.table_name, 
  c.column_name 
from 
  systab as t key join systabcol as c 
where 
   c.column_name = 'MyColumnName'

其它参考28


declare @ColumnName nvarchar(max)='QbId'

Select b.Name as TableName from Sys.Columns a
Join Sys.Tables b
on a.Object_Id=b.Object_Id
where a.Name=@ColumnName