是否可以查询包含以下列的表名
LIKE '%myName%'
?
是否可以查询包含以下列的表名
LIKE '%myName%'
?
当前回答
我出于同样的目的使用了这个,它起到了作用:
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG= 'theDatabase'
and COLUMN_NAME like 'theCol%'
其他回答
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
我出于同样的目的使用了这个,它起到了作用:
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG= 'theDatabase'
and COLUMN_NAME like 'theCol%'
下面是Sybase数据库的工作解决方案
select
t.table_name,
c.column_name
from
systab as t key join systabcol as c
where
c.column_name = 'MyColumnName'
您可以尝试以下查询:
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
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