declare @str varchar(100) set @str='101' --要搜索的字符串 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
--搜索指定数据在那个对象中存在CREATE PROC sp_ValueSearch @value sql_variant, --要搜索的数据 @precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配 AS SET NOCOUNT ON IF @value IS NULL RETURN--数据类型处理 SELECT xtype INTO #t FROM systypes WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')--扩展数据类型及查询处理语句 DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000) IF @precision=1 SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType') WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%''' WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%''' ELSE N'=@value' END ELSE BEGIN SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname) IF @sql LIKE N'%char' or @sql LIKE N'%text' BEGIN INSERT #t SELECT xtype FROM systypes WHERE name LIKE N'%char' or name LIKE N'%text' SELECT @sql=N' LIKE N''%''+CAST(@value as ' +CASE WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)' ELSE 'varchar(8000)' END +N')+N''%''' END ELSE IF @sql LIKE N'%datetime' BEGIN INSERT #t SELECT xtype FROM systypes WHERE name LIKE N'%datetime' SET @sql=N'=@value' END ELSE IF @sql LIKE N'%int' OR @sql LIKE N'%money' OR @sql IN(N'real',N'float',N'decimal',N'numeric') BEGIN INSERT #t SELECT xtype FROM systypes WHERE name LIKE N'%int' OR name LIKE N'%money' OR name IN(N'real',N'float',N'decimal') SET @sql=N'=@value' END ELSE SET @sql=N'=@value' END --保存结果的临时表 CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))DECLARE tb CURSOR LOCAL FOR SELECT N'SELECT * FROM ' +QUOTENAME(USER_NAME(o.uid)) +N'.'+QUOTENAME(o.name) +N' WHERE '+QUOTENAME(c.name) +@sql, N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''') +N',N'+QUOTENAME(c.name,N'''') +N',N'+QUOTENAME(QUOTENAME(t.name)+CASE WHEN t.name IN (N'decimal',N'numeric') THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')' WHEN t.name=N'float' OR t.name like N'%char' OR t.name like N'%binary' THEN N'('+CAST(c.prec as varchar)+N')' ELSE N'' END,N'''') +N',@sql)' FROM sysobjects o,syscolumns c,systypes t,#t tt WHERE o.id=c.id AND c.xusertype=t.xusertype AND t.xtype=tt.xtype AND OBJECTPROPERTY(o.id,N'IsUserTable')=1OPEN tb FETCH tb INTO @sql,@sql1 WHILE @@FETCH_STATUS=0 BEGIN SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1 EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql FETCH tb INTO @sql,@sql1 END CLOSE tb DEALLOCATE tb SELECT * FROM #
--假如有学号这个字段的话 select a.name 所在表名 from sysobjects a,syscolumns b where a.id=b.id and b.name='学号' and a.xtype='u'--查出表名以后,你在select * from tb where 学号='101'
再看看邹老大写的这个:CREATE PROC sp_FindObject @objectname sysname, --要查找的对象名 @whereand nvarchar(2000)=N'', --数据库的过滤条件 @operator nchar(2)=N'=' --查找对象的运算符 AS SET NOCOUNT ON --保存搜索结果的临时表 CREATE TABLE #(DatabaseName sysname,ObjectName sysname,Type sysname)--定义 sp_MSforeach_worker 检索数据使用的游标 IF @whereand IS NULL SET @whereand='' EXEC(' DECLARE hCForEach CURSOR GLOBAL FOR SELECT name FROM master.dbo.sysdatabases WHERE HAS_DBACCESS(name)=1 '+@whereand) DECLARE @sql nvarchar(4000) SET @sql=N'INSERT # SELECT N''?'',name,Type=CASE xtype WHEN N''C'' THEN N''CHECK约束'' WHEN N''TF'' THEN N''表函数'' WHEN N''D'' THEN N''默认值或DEFAULT约束'' WHEN N''TR'' THEN N''触发器'' WHEN N''F'' THEN N''FOREIGN KEY约束'' WHEN N''U'' THEN N''用户表'' WHEN N''L'' THEN N''日志'' WHEN N''V'' THEN N''视图'' WHEN N''FN'' THEN N''标量函数'' WHEN N''X'' THEN N''扩展存储过程'' WHEN N''IF'' THEN N''内嵌表函数'' WHEN N''R'' THEN N''规则'' WHEN N''P'' THEN N''存储过程'' WHEN N''PK'' THEN N''PRIMARY KEY 约束'' WHEN N''RF'' THEN N''复制筛选存储过程'' WHEN N''UQ'' THEN N''UNIQUE约束'' WHEN N''S'' THEN N''系统表'' ELSE N''未知'' END FROM [?].dbo.sysobjects WHERE name ' +CASE WHEN @operator IN('=','>','>=','!>','<','<=','!<','<>','!=') THEN @operator+QUOTENAME(@objectname,'''') WHEN @operator='IN' THEN @operator+N' IN('+QUOTENAME(@objectname,'''')+')' WHEN @operator IN('LIKE','%') THEN ' LIKE '+QUOTENAME(@objectname,'''') ELSE '='+QUOTENAME(@objectname,'''') END EXEC sp_MSforeach_worker @command1=@sql SELECT * FROM #
这样也可以吧~~查表名: SELECT [TNAME]=TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE [COLUMN_NAME]='学号'然后在根据表名[TNAME]查 “学号=101” 的记录
set @str='101' --要搜索的字符串
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
@value sql_variant, --要搜索的数据
@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
ELSE N'=@value' END
ELSE
BEGIN
SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
IF @sql LIKE N'%char' or @sql LIKE N'%text'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%char' or name LIKE N'%text'
SELECT @sql=N' LIKE N''%''+CAST(@value as '
+CASE
WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
ELSE 'varchar(8000)' END
+N')+N''%'''
END
ELSE IF @sql LIKE N'%datetime'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%datetime'
SET @sql=N'=@value'
END
ELSE IF @sql LIKE N'%int'
OR @sql LIKE N'%money'
OR @sql IN(N'real',N'float',N'decimal',N'numeric')
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%int'
OR name LIKE N'%money'
OR name IN(N'real',N'float',N'decimal')
SET @sql=N'=@value'
END
ELSE
SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+QUOTENAME(o.name)
+N' WHERE '+QUOTENAME(c.name)
+@sql,
N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE
WHEN t.name IN (N'decimal',N'numeric')
THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
WHEN t.name=N'float'
OR t.name like N'%char'
OR t.name like N'%binary'
THEN N'('+CAST(c.prec as varchar)+N')'
ELSE N'' END,N'''')
+N',@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
AND c.xusertype=t.xusertype
AND t.xtype=tt.xtype
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #
select a.name 所在表名 from sysobjects a,syscolumns b where a.id=b.id and b.name='学号' and a.xtype='u'--查出表名以后,你在select * from tb where 学号='101'
@objectname sysname, --要查找的对象名
@whereand nvarchar(2000)=N'', --数据库的过滤条件
@operator nchar(2)=N'=' --查找对象的运算符
AS
SET NOCOUNT ON
--保存搜索结果的临时表
CREATE TABLE #(DatabaseName sysname,ObjectName sysname,Type sysname)--定义 sp_MSforeach_worker 检索数据使用的游标
IF @whereand IS NULL SET @whereand=''
EXEC('
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT name FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name)=1 '+@whereand)
DECLARE @sql nvarchar(4000)
SET @sql=N'INSERT # SELECT N''?'',name,Type=CASE xtype
WHEN N''C'' THEN N''CHECK约束''
WHEN N''TF'' THEN N''表函数''
WHEN N''D'' THEN N''默认值或DEFAULT约束''
WHEN N''TR'' THEN N''触发器''
WHEN N''F'' THEN N''FOREIGN KEY约束''
WHEN N''U'' THEN N''用户表''
WHEN N''L'' THEN N''日志''
WHEN N''V'' THEN N''视图''
WHEN N''FN'' THEN N''标量函数''
WHEN N''X'' THEN N''扩展存储过程''
WHEN N''IF'' THEN N''内嵌表函数''
WHEN N''R'' THEN N''规则''
WHEN N''P'' THEN N''存储过程''
WHEN N''PK'' THEN N''PRIMARY KEY 约束''
WHEN N''RF'' THEN N''复制筛选存储过程''
WHEN N''UQ'' THEN N''UNIQUE约束''
WHEN N''S'' THEN N''系统表''
ELSE N''未知'' END
FROM [?].dbo.sysobjects
WHERE name '
+CASE
WHEN @operator IN('=','>','>=','!>','<','<=','!<','<>','!=')
THEN @operator+QUOTENAME(@objectname,'''')
WHEN @operator='IN'
THEN @operator+N' IN('+QUOTENAME(@objectname,'''')+')'
WHEN @operator IN('LIKE','%')
THEN ' LIKE '+QUOTENAME(@objectname,'''')
ELSE '='+QUOTENAME(@objectname,'''')
END
EXEC sp_MSforeach_worker @command1=@sql
SELECT * FROM #
SELECT [TNAME]=TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [COLUMN_NAME]='学号'然后在根据表名[TNAME]查 “学号=101” 的记录