这是某个朋友需要查一个数据，但他不知道存在什么地方，所以花了一个小时帮他写了个查询，能够对逐个字段进行检查。SQL2005 下运行通过。效率可能不高。没有优化。
全局查询nvarchar,text在数据库中的匹配项
DECLARE @TableName nvarchar(300)
DECLARE @COLUMNSName nvarchar(300)
DECLARE @SQl nvarchar(Max)
DECLARE @Num int
DECLARE @SearchKey nvarchar(200) /*查询的文字*/
set @SearchKey='test'
set @Num=1
DECLARE TableNameCOLUMNS CURSOR FOR
select Table_Name,Column_Name from INFORMATION_SCHEMA.COLUMNS where data_type ='nchar' or
data_type ='ntext' or data_type ='nvarchar' or data_type ='varchar'
OPEN TableNameCOLUMNS
FETCH next FROM TableNameCOLUMNS INTO @TableName, @COLUMNSName
WHILE (@@fetch_status = 0)
BEGIN
if @Num=1
BEGIN
set @SQl=' select ('''+@COLUMNSName+''') as COLUMNSName ,'''+ @TableName+''' as TableName,count(*) as num from [' + @TableName +'] where ['+ @COLUMNSName +'] like N''%'+@SearchKey+'%'''+char(13)
end
else
BEGIN
SET @SQl =@SQl+ ' Union ' +' select ('''+@COLUMNSName+''') as COLUMNSName,'''+ @TableName+''' as [TableName],count(*) as num from [' + @TableName +'] where ['+ @COLUMNSName +'] like N''%'+@SearchKey+'%'''+char(13)
FETCH next FROM TableNameCOLUMNS INTO @TableName, @COLUMNSName
end
set @Num=@Num+1
END
CLOSE TableNameCOLUMNS
DEALLOCATE TableNameCOLUMNS
set @SQl='select * from ('+@SQl+') a where a.Num>0 order by a.TableName'
exec sp_executesql @SQL
--------------------------------------------------------------------------------------------------------------------
