Question

如何sqlserver2005从整个库中查找某个值？

如题，是从整个数据库中查找含有某个值的字段名。。。该怎么些sql语句？

Answer 1

以下为在SQL Server中查看某个表的字段信息的查询语句（以数据库Pubs的jobs表为例）

SELECT SysObjects.Name as TableName,
SysColumns.Name as ColumnsName,
SysTypes.Name as DateType,
SysColumns.Length as DateLength,
SysProperties.Value as Remark --列描述
FROM SysObjects,
SysTypes,
SysColumns
LEFT JOIN SysProperties
ON (Syscolumns.Id = Sysproperties.Id AND Syscolumns.Colid = Sysproperties.Smallid)
WHERE (Sysobjects.Xtype =u OR Sysobjects.Xtype =v)
AND Sysobjects.Id = Syscolumns.Id
AND SysTypes.XType = Syscolumns.XType
AND SysTypes.Name <> sysname
AND SysObjects.name = jobs
如果需要查询整个数据库的所有表的话，就把最后哪个条件去掉即可。

Answer 2

declare @sql varchar(max);
set @sql='';
select @sql=@sql+'if exists (select * from '+o.[name]+' where '+c.[name]+' like ''%你的特定值%'')
select '''+o.[name]+''' tabname,'''+c.[name]+''' colname ;'+CHAR(10)
from sys.columns c join sys.objects o
on c.[object_id]=o.[object_id]
where (max_length>=4 or max_length=-1)
and system_type_id in (35,99,168,175,231,239,241)
and o.[type]='U'
print @sql;
exec(@sql)