1.Oracle：
select table_name
from user_tab_columns
where COLUMN_NAME= ‘SAL’—查詢資料庫内所有包含sal列的表名
2.SqlServer
select table_name
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME= ‘ksmc’
INTERSECT
select b.name
from sysindexes a ,
sysobjects b
where a.id = b.id
and a.indid < 2
and a.rowcnt > 0
and objectproperty(b.id, ‘IsMSShipped’) = 0——-查詢資料庫内包含某個列名，并且存在資料的表名
從整個資料庫内查詢含有某個字元串的表名(工作量極大，慎用)
declare @cloumns varchar(40)
declare @tablename varchar(40)
declare @str varchar(40)
declare @counts int
declare @sql nvarchar(2000)
declare MyCursor Cursor For
Select a.name as Columns, b.name as TableName from syscolumns a,sysobjects b,systypes c
where a.id = b.id
and b.type = ‘U’
and a.xtype=c.xtype
and c.name like ‘%char%’
set @str=’林麗英’ —此位置為查找的字元
Open MyCursor
Fetch next From MyCursor Into @cloumns,@tablename
While(@@Fetch_Status = 0)
Begin
set @sql=’select @tmp_counts=count(*) from ‘ [email protected]+ ‘ where ‘ [email protected]+’ like ”%’ [email protected]+ ‘%”’
execute sp_executesql @sql,N’@tmp_counts int out’,@counts out
if @counts>0
begin
print ‘表名為:’[email protected]+’,字段名為’[email protected]
end
Fetch next From MyCursor Into @cloumns,@tablename
End
Close MyCursor
Deallocate MyCursor