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

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的任何引用!将这些结果写入文件,以备不时之需。

注意***在运行delete语句之前,请确保消除所有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%'

其他回答

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

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

这应该是有效的:

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

希望这不是一个重复的答案,但我想做的是在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的任何引用!将这些结果写入文件,以备不时之需。

注意***在运行delete语句之前,请确保消除所有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%'

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

来自Pinal Sir博客