Sql Server

首页 / 知识 / Sql Server 
DECLARE @SearchText NVARCHAR(100)
SET @SearchText = N'%搜索的文本%'
 
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = STUFF((SELECT ',' + QUOTENAME(COLUMN_NAME)
                  FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = '你的表名'
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
 
SET @SQL = 'SELECT * FROM 你的表名 WHERE ' + 
           STUFF((SELECT ' OR ' + QUOTENAME(COLUMN_NAME) + ' LIKE @SearchText'
                  FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = '你的表名'
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 4, '')
 
EXEC sp_executesql @SQL, N'@SearchText NVARCHAR(100)', @SearchText

生成的SQL，形如

SELECT * FROM table1 
WHERE 
[xtwpks] LIKE @SearchText 
OR [xtwpmc] LIKE @SearchText 
OR [计划价] LIKE @SearchText