数据库寻找一个string所在的表
寻找一个string所在的表
方法1偶尔会出错
[sql]
--方法1
declare @ssearchcontent varchar(100)='f6f89834-817c-47d7-a436-6484754111cb'
-----------------------------------------------------------------------------------------------
declare @table table(id int identity(1,1),columnname varchar(100),tablename varchar(100))
declare @line int = 1
declare @total int
declare @sql nvarchar(4000)
declare @columnname varchar(100)
declare @tablename varchar(100)
declare @num int
insert into @table
select a.name,object_name(a.object_id) as tablename from sys.columns as a inner join sysobjects as b on a.object_id = b.id and b.xtype = 'u' and system_type_id = 167
select @total = count(1) from @table
while @line <= @total
begin
select @columnname = columnname,@tablename = tablename
from @table
where id = @line
--set @sql='select @a=count(*) from ' + @tablename + ' where ' + @columnname + ' = ''刘八'''
set @sql='select @a=count(*) from ' + @tablename + ' where ' + @columnname + ' = ''' + @ssearchcontent + ''''
exec sp_executesql @sql,n'@a int output',@num output
if @num > 0
begin
select @tablename as tablename,@columnname as columnname
end
set @line = @line + 1
end
-----------------------------------------------------------------------------------------------
--方法2
declare @str varchar(100)
set @str='f6f89834-817c-47d7-a436-6484754111cb'
----------------------------------------------------------------------------------------------
declare @s varchar(8000)
declare tb cursor local for
select '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
----------------------------------------------------------------------------------------------
作者:keenweiwei
推荐阅读
-
Mysql中返回一个数据库的所有表名,列名数据类型备注
-
Mysql中返回一个数据库的所有表名,列名数据类型备注
-
SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
-
mysql怎么把一个数据库的表复制到另一个数据库中
-
备份mysql数据库的php代码(一个表一个文件)
-
我们在删除SQL Sever某个数据库表中数据的时候,希望ID重新从1开始,而不是紧跟着最后一个ID开始需要的命令
-
SQLSERVER查询整个数据库中某个特定值所在的表和字段的方法
-
apue_db:一个可以充当"注册表"的 key-value 数据库
-
Oracle Sql修改数据库表中的一个字段可为空 OracleSQL
-
Oracle Sql修改数据库表中的一个字段可为空 OracleSQL