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

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

其他回答

如果只需要表名,可以运行:

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年添加的。对于2008r2和更早版本,使用+连接字符串。

自从我发布这个之后,我已经重新格式化了进程。它现在有点高级了,但看起来更乱了(但它在一个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

它将返回table_name、schema_name、column_name

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 '%colName%'
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博客

如果你更喜欢第三方工具,有很多选择,比如:

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

如果您的数据库包含加密对象(视图、过程、函数),则这些对象非常有用,因为您无法使用系统表轻松搜索这些对象。

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