遍历表所有栏位查找记录: DECLARE @Sql NVARCHAR(max)='' SELECT @Sql = @Sql + ' OR ' + name + ' like ''%姓名:张小凡%''' FROM sys.columns AS a WHERE object_id = OBJECT_ID('表') AND EXISTS(SELECT * FROM sys.types WHERE user_type_id=a.user_type_id AND name IN('varchar','nvarchar'))--加上栏位类型过滤非字符串栏位SET @Sql=STUFF(@Sql,1,3,'SELECT * FROM 表 WHERE ') EXEC(@Sql)
和版主类似,如果以下脚本执行出来的结果是你想要的结果的话,保存到另外一张表就容易多了DECLARE @SQL NVARCHAR(MAX) SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT SUBSTRING([' + [name] + '],4,LEN([' + [name] + ']))[姓名]FROM[表名]WHERE[' + [name] + ']LIKE ''姓名:%''' FROM SYS.SYSCOLUMNS WHERE id=OBJECT_ID('表名')
SET @SQL=STUFF(@SQL,1,11,'') --PRINT @SQL EXEC(@SQL)
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql = @Sql + ' OR ' + name + ' like ''%姓名:张小凡%'''
FROM sys.columns AS a
WHERE object_id = OBJECT_ID('表') AND EXISTS(SELECT * FROM sys.types WHERE user_type_id=a.user_type_id AND name IN('varchar','nvarchar'))--加上栏位类型过滤非字符串栏位SET @Sql=STUFF(@Sql,1,3,'SELECT * FROM 表 WHERE ')
EXEC(@Sql)
SET @SQL=''
SELECT @SQL=@SQL+' UNION ALL SELECT SUBSTRING([' + [name] + '],4,LEN([' + [name] + ']))[姓名]FROM[表名]WHERE[' + [name] + ']LIKE ''姓名:%'''
FROM SYS.SYSCOLUMNS
WHERE id=OBJECT_ID('表名')
SET @SQL=STUFF(@SQL,1,11,'')
--PRINT @SQL
EXEC(@SQL)