1、怎么查找整个数据库列出包含有列名为mycol的所有表?
2、怎么查找整个数据库列出值为myval的所有表和列?
9 个解决方案
#1
1.select sysobjects.name
from syscolumns
inner join sysobjects on syscolumns.id=sysobjects.id
where syscolumns.name='name'
from syscolumns
inner join sysobjects on syscolumns.id=sysobjects.id
where syscolumns.name='name'
#2
2.做全文索引好像能做到
#3
--1、怎么查找整个数据库列出包含有列名为mycol的所有表?
Select A.Name From SysObjects A Inner Join SysColumns B On A.ID = B.ID And B.Name = 'mycol'
Select A.Name From SysObjects A Inner Join SysColumns B On A.ID = B.ID And B.Name = 'mycol'
#4
主要是第二个问题具体怎么解决?
#5
1. select TABLE_NAME from information_schema.columns where column_name='mycol'
2 .全文索引
2 .全文索引
#6
2: 给定一个任意的字段的值,查询出它属于的表及哪个字段的SQL语句:
declare @str varchar(100)
set @str='PH' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
declare @str varchar(100)
set @str='PH' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
#7
楼上的那句运行怎么没输出的?请教~
#8
我试了下,可以啊.
收藏了.
收藏了.
#9
--搜索的关键字(值)对吗?
declare @str varchar(100)
set @str='myval' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
declare @str varchar(100)
set @str='myval' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
#1
1.select sysobjects.name
from syscolumns
inner join sysobjects on syscolumns.id=sysobjects.id
where syscolumns.name='name'
from syscolumns
inner join sysobjects on syscolumns.id=sysobjects.id
where syscolumns.name='name'
#2
2.做全文索引好像能做到
#3
--1、怎么查找整个数据库列出包含有列名为mycol的所有表?
Select A.Name From SysObjects A Inner Join SysColumns B On A.ID = B.ID And B.Name = 'mycol'
Select A.Name From SysObjects A Inner Join SysColumns B On A.ID = B.ID And B.Name = 'mycol'
#4
主要是第二个问题具体怎么解决?
#5
1. select TABLE_NAME from information_schema.columns where column_name='mycol'
2 .全文索引
2 .全文索引
#6
2: 给定一个任意的字段的值,查询出它属于的表及哪个字段的SQL语句:
declare @str varchar(100)
set @str='PH' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
declare @str varchar(100)
set @str='PH' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
#7
楼上的那句运行怎么没输出的?请教~
#8
我试了下,可以啊.
收藏了.
收藏了.
#9
--搜索的关键字(值)对吗?
declare @str varchar(100)
set @str='myval' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
declare @str varchar(100)
set @str='myval' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb