T-SQL 模糊和精确查找
以下语句类似查找中的 LIKE , EXACT.
-- Select Search criteria, If not WILDCARDMATCH, will search for Exact Match DECLARE @SEARCHCRETERIA NVARCHAR(1000) = 'WILDCARDMATCH'-- Enter Searching value DECLARE @SEARCH NVARCHAR(1000) = 'Australia' DECLARE @ROWCOUNT INT;DECLARE @COLUMNNAME NVARCHAR(1000);DECLARE @TABLENAME NVARCHAR(1000);DECLARE @STAT NVARCHAR(2000);DECLARE @TABLE TABLE (COLUMN_NAME VARCHAR(1000),TABLE_NAME VARCHAR(1000))DECLARE @INTABLE TABLE (COLUMN_NAME VARCHAR(1000),TABLE_NAME VARCHAR(1000))DECLARE @RESULTTABLE TABLE (COLUMN_NAME VARCHAR(1000),COLUMN_VALUE VARCHAR(1000))INSERT INTO @TABLESELECT '[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']' AS 'TargetColumn','[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS 'TargetTable'FROM INFORMATION_SCHEMA.COLUMNSWHERE DATA_TYPE <> 'XML'SELECT @ROWCOUNT = COUNT(*)FROM @TABLEWHILE (@ROWCOUNT != 0)BEGININSERT INTO @INTABLESELECT TOP 1 *FROM @TABLEIF (@SEARCHCRETERIA = 'WILDCARDMATCH')SELECT @STAT = 'SELECT ' + '''' + COLUMN_NAME + '''' + ',' + COLUMN_NAME + ' FROM ' + TABLE_NAME + ' WHERE CONVERT( VARCHAR,' + COLUMN_NAME + ') LIKE ' + '''%' + @SEARCH + '%'''FROM @INTABLEELSESELECT @STAT = 'SELECT ' + '''' + COLUMN_NAME + '''' + ',' + COLUMN_NAME + ' FROM ' + TABLE_NAME + ' WHERE CONVERT( VARCHAR,' + COLUMN_NAME + ') = ' + '''' + @SEARCH + ''''FROM @INTABLEINSERT INTO @RESULTTABLEEXEC (@STAT)DELETEFROM @INTABLEDELETE TOP (1)FROM @TABLESELECT @ROWCOUNT = COUNT(*)FROM @TABLEENDSELECT *FROM @RESULTTABLE
需要指出的是:
不能查找XML
可以使用DISTINCT在最后,去掉重复记录.
