SELECT table_name FROM information_schema.columns
WHERE column_name = 'product_name'
我希望扩展我的查询能力,可以搜索所有数据库,甚至查找存储过程中包含我搜索列名称的内容。
SELECT table_name FROM information_schema.columns
WHERE column_name = 'product_name'
此脚本将在所有数据库的所有表中搜索您的列。
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 ''%yourcolumnname%'''
select * from #yourcolumndetails
Drop table #yourcolumndetails
SELECT
OBJECT_NAME(object_id) as 'Procedure/Function/View Name',
definition
--,*
FROM sys.sql_modules
WHERE
( definition LIKE '%' + '<Your_Field_Name>' + '%' )
select
s.name as SchemaName,
objs.name as ObjectName,
objs.ObjectType,
OBJECT_DEFINITION(objs.object_id) AS ObjectDefinition
from
(SELECT object_id, name, 'Proc' as ObjectType
FROM sys.procedures p
UNION
select object_id, name, 'View'
from sys.views
UNION
SELECT object_id, Name, type
FROM sys.objects o
WHERE o.[type] IN ('fn', 'fs', 'ft', 'if', 'tf')) objs
inner join sys.objects o
on objs.object_id=o.object_id
inner join sys.schemas s
on o.schema_id=s.schema_id
列(例如在服务器上的任何数据库中查找抽象表中的列),您可以执行相同的操作并只是联合信息模式,但这对我想要的不起作用,因此我正在寻找更通用的解决方案-如果我找到它,我会添加它!
要在存储过程中搜索,请使用information_schema.routines
DECLARE @colName NVARCHAR(30)
DECLARE @insertCMD NVARCHAR(256)
CREATE TABLE #yourcolumndetails ( DBaseName VARCHAR(100) ,TableSchema VARCHAR(50) ,TableName VARCHAR(100) ,ColumnName VARCHAR(100) ,DataType VARCHAR(100) ,CharMaxLength VARCHAR(100) )
SET @colName = UPPER('yourColumName')
SET @colName = '''%' + @colName + '%'''
PRINT @colName
SET @insertCMD = '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' + @colName
PRINT @insertCMD
EXEC sp_MSForEachDB @command1 = @insertCMD
SELECT * FROM #yourcolumndetails
DROP TABLE #yourcolumndetails
select * from information_schema.tables
where table_name like upper('%email%'); '
解释:
Table_name 是列的名称
所有帮助我找到列“email”的命令
>
所采取的步骤:
show databases ;
show tables;
select * from information_schema.tables;
select * from information_schema.tables
where table_name like upper('%email%');'
您在没有MySQL管理员权限的情况下运行此查询,因此它只会显示您可以访问的数据库/表