MSSQL中遍历数据库查找匹配值
declare @value VARCHAR(1024)
--设置需要搜索的文本
set @value='张三'
SET NOCOUNT ON
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
--申明临时表
CREATE TABLE #t(
tablename VARCHAR(64),
columnname VARCHAR(64),
strSql VARCHAR(MAX)
)
--申明游标,遍历表字符串列
DECLARE tableCursor CURSOR FOR
SELECT o.name, c.name FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id inner join systypes t on c.xusertype=t.xtype
WHERE o.type = 'U' AND t.name IN ('varchar','char','nvarchar','nchar') ORDER BY o.name, c.name
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @table,@column
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''','+'''SELECT * from '+@table+' where '+@column+' like ''''%'+@value+'%'''''')'
EXEC(@sql)
--print @sql
FETCH NEXT FROM tableCursor INTO @table,@column
END
CLOSE tableCursor
DEALLOCATE tableCursor
--输出结果
SELECT tablename as 表名,columnname as 列名,strSql AS 查询语句 FROM #t
DROP TABLE #t
SET NOCOUNT OFF
