USE db_name GOSELECT o.name,c.name FROM sysobjects AS o JOIN syscolumns AS c ON o.id=c.id
select Name from sysobjects where xtype='u' and status>=0 and id in (select id from syscolumns where name='字段')select object_name(id) 对象名 from syscolumns where name='字段'
一個字段在哪個表:use dbnameselect c.name,o.name from sysobjects as o join syscolumns as c on o.id=c.id where c.name='一个值'
declare @str varchar(100) set @str='U8中的销售发票、采购发票 关键字' --要搜索的字符串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
select c.name,o.name from sysobjects as o join syscolumns as c on o.id=c.id where c.name='知道的值'
大概是這樣,但一個變量不夠declare @sql varchar(8000),@value varchar(100) set @value='xxx' set @sql='' select @sql=@sql+'select '''+c.name+''' as FieldName, '''+o.name+''' as TableName from '+o.name+ ' where '+c.name+'='''+@value+''' union all ' from sysobjects as o join syscolumns as c on o.id=c.id where o.xtype='U'set @sql=left(@sql,len(@sql)-9) --print @sql exec(@sql)
取出所有数据库的表和字段 SELECT d.name , a.name FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams' Order by a.id,a.colorder 不过不知道你的数据库有多大,是用循环还是用游标就要自己考虑了!
将值 cast为varbinary,把值粘出来,然后 用ue打开mdf文件,去搜索了。
写了一个存储过程alter PROC getTableName (@variable varchar(100) ---要查询的值 ) as CREATE table #aa(id int) create table #Temp1(deptid varchar(100),username varchar(100)) INSERT INTO #Temp1 SELECT d.name , a.name FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams' Order by a.id,a.colorder
create table #Temp2(deptid varchar(100),username varchar(100))declare @deptid varchar(100),@username varchar(100) declare Select_cursor cursor for select deptid,username from #Temp1 open Select_cursor fetch next from Select_cursor into @deptid,@username while @@fetch_status=0 BEGIN DECLARE @sql nvarchar(max) SET @sql='if exists(select 1 from '''+@deptid+''' where '''+@username+'''='''+@variable+''') INSERT INTO #Temp2(deptid ,username) select '''+@deptid+','+@username+'''' EXEC(@sql)fetch next from Select_cursor into @deptid,@username end close Select_cursor deallocate Select_cursorselect DISTINCT * from #Temp2 --测试结果 Drop table #Temp1,#Temp2
测试的时候发现上面的有的字段通不过,修改了一下!--EXEC getTableName '001' create PROC getTableName (@variable varchar(100) ---要查询的值 ) as CREATE table #aa(id int) create table #Temp1(deptid varchar(100),username varchar(100)) INSERT INTO #Temp1 SELECT d.name , a.name FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams' Order by a.id,a.colorder
create table #Temp2(deptid varchar(100),username varchar(100))declare @deptid varchar(100)DECLARE @username varchar(100) declare Select_cursor cursor for select deptid,username from #Temp1 open Select_cursor fetch next from Select_cursor into @deptid,@username while @@fetch_status=0 BEGIN DECLARE @sql nvarchar(max) SET @sql='if exists(select 1 from '+@deptid+' where '+@username+'='''+@variable+''') INSERT INTO #Temp2(deptid ,username) values( '''+@deptid+''','''+@username+''')' --PRINT(@sql) EXEC(@sql)fetch next from Select_cursor into @deptid,@username end close Select_cursor deallocate Select_cursorselect DISTINCT * from #Temp2 --测试结果 Drop table #Temp1,#Temp2
还是有问题字段类型问题要跟据具体内容人为控制 --EXEC getTableName '001' create PROC getTableName (@variable varchar(100) ---要查询的值 ) as CREATE table #aa(id int) create table #Temp1(deptid varchar(100),username varchar(100)) INSERT INTO #Temp1 SELECT d.name , a.name FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams' WHERE b.name<>'uniqueidentifier' AND b.name<>'datetime' AND b.name<>'int' ---根据值的类型不同在这里进行过滤 Order by a.id,a.colorder
create table #Temp2(deptid varchar(100),username varchar(100))declare @deptid varchar(100)DECLARE @username varchar(100)declare Select_cursor cursor for select deptid,username from #Temp1 open Select_cursor fetch next from Select_cursor into @deptid,@username while @@fetch_status=0 BEGIN DECLARE @sql nvarchar(max) SET @sql='if exists(select 1 from ['+@deptid+'] where ['+@username+']='''+ @variable+''') INSERT INTO #Temp2(deptid ,username) values( '''+@deptid+''','''+@username+''')' PRINT(@sql) EXEC(@sql)fetch next from Select_cursor into @deptid,@username end close Select_cursor deallocate Select_cursorselect DISTINCT * from #Temp2 --测试结果 Drop table #Temp1,#Temp2
GOSELECT o.name,c.name
FROM sysobjects AS o
JOIN syscolumns AS c
ON o.id=c.id
from sysobjects as o join syscolumns as c on o.id=c.id
where c.name='一个值'
set @str='U8中的销售发票、采购发票 关键字' --要搜索的字符串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
from sysobjects as o join syscolumns as c on o.id=c.id
where c.name='知道的值'
set @value='xxx'
set @sql=''
select @sql=@sql+'select '''+c.name+''' as FieldName, '''+o.name+''' as TableName from '+o.name+ ' where '+c.name+'='''+@value+''' union all '
from sysobjects as o join syscolumns as c on o.id=c.id
where o.xtype='U'set @sql=left(@sql,len(@sql)-9)
--print @sql
exec(@sql)
SELECT d.name ,
a.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams'
Order by a.id,a.colorder
不过不知道你的数据库有多大,是用循环还是用游标就要自己考虑了!
用ue打开mdf文件,去搜索了。
(@variable varchar(100) ---要查询的值
)
as
CREATE table #aa(id int)
create table #Temp1(deptid varchar(100),username varchar(100))
INSERT INTO #Temp1 SELECT d.name ,
a.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams'
Order by a.id,a.colorder
create table #Temp2(deptid varchar(100),username varchar(100))declare @deptid varchar(100),@username varchar(100)
declare Select_cursor cursor for
select deptid,username from #Temp1
open Select_cursor
fetch next from Select_cursor into @deptid,@username
while @@fetch_status=0
BEGIN
DECLARE @sql nvarchar(max)
SET @sql='if exists(select 1 from '''+@deptid+''' where '''+@username+'''='''+@variable+''') INSERT INTO #Temp2(deptid ,username) select '''+@deptid+','+@username+''''
EXEC(@sql)fetch next from Select_cursor into @deptid,@username
end
close Select_cursor
deallocate Select_cursorselect DISTINCT * from #Temp2 --测试结果
Drop table #Temp1,#Temp2
create PROC getTableName
(@variable varchar(100) ---要查询的值
)
as
CREATE table #aa(id int)
create table #Temp1(deptid varchar(100),username varchar(100))
INSERT INTO #Temp1 SELECT d.name ,
a.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams'
Order by a.id,a.colorder
create table #Temp2(deptid varchar(100),username varchar(100))declare @deptid varchar(100)DECLARE @username varchar(100)
declare Select_cursor cursor for
select deptid,username from #Temp1
open Select_cursor
fetch next from Select_cursor into @deptid,@username
while @@fetch_status=0
BEGIN
DECLARE @sql nvarchar(max)
SET @sql='if exists(select 1 from '+@deptid+' where '+@username+'='''+@variable+''') INSERT INTO #Temp2(deptid ,username) values( '''+@deptid+''','''+@username+''')'
--PRINT(@sql)
EXEC(@sql)fetch next from Select_cursor into @deptid,@username
end
close Select_cursor
deallocate Select_cursorselect DISTINCT * from #Temp2 --测试结果
Drop table #Temp1,#Temp2
--EXEC getTableName '001'
create PROC getTableName
(@variable varchar(100) ---要查询的值 )
as
CREATE table #aa(id int)
create table #Temp1(deptid varchar(100),username varchar(100))
INSERT INTO #Temp1 SELECT d.name ,
a.name
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' AND d.name<>'sysdiagrams'
WHERE b.name<>'uniqueidentifier' AND b.name<>'datetime' AND b.name<>'int' ---根据值的类型不同在这里进行过滤
Order by a.id,a.colorder
create table #Temp2(deptid varchar(100),username varchar(100))declare @deptid varchar(100)DECLARE @username varchar(100)declare Select_cursor cursor for
select deptid,username from #Temp1
open Select_cursor
fetch next from Select_cursor into @deptid,@username
while @@fetch_status=0
BEGIN
DECLARE @sql nvarchar(max)
SET @sql='if exists(select 1 from ['+@deptid+'] where ['+@username+']='''+ @variable+''') INSERT INTO #Temp2(deptid ,username) values( '''+@deptid+''','''+@username+''')'
PRINT(@sql)
EXEC(@sql)fetch next from Select_cursor into @deptid,@username
end
close Select_cursor
deallocate Select_cursorselect DISTINCT * from #Temp2 --测试结果
Drop table #Temp1,#Temp2