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




sql-server tsql (19)

搜索表格:

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;

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

LIKE '%myName%'


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

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

你可以试试这个查询:

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

如果你只是想要你可以运行的表名:

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

值得注意的是,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

对于Oracle,具有正常的用户权限:

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

就像oracle一样,你可以找到这样的表格和列:

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


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

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

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

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

我刚刚尝试过,并且完美地工作

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 COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%col_Name%'

这应该工作:

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_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 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  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;

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

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;

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


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 [email protected]

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

有用





system-tables