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 @TABLE
SELECT '[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']' AS 'TargetColumn'
,'[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS 'TargetTable'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE <> 'XML'

SELECT @ROWCOUNT = COUNT(*)
FROM @TABLE

WHILE (@ROWCOUNT != 0)
BEGIN
INSERT INTO @INTABLE
SELECT TOP 1 *
FROM @TABLE

IF (@SEARCHCRETERIA = 'WILDCARDMATCH')
SELECT @STAT = 'SELECT ' + '''' + COLUMN_NAME + '''' + ',' + COLUMN_NAME + ' FROM ' + TABLE_NAME + 
' WHERE CONVERT( VARCHAR,' + COLUMN_NAME + ') LIKE ' + '''%' + @SEARCH + '%'''
FROM @INTABLE
ELSE
SELECT @STAT = 'SELECT ' + '''' + COLUMN_NAME + '''' + ',' + COLUMN_NAME + ' FROM ' + TABLE_NAME + 
' WHERE CONVERT( VARCHAR,' + COLUMN_NAME + ') = ' + '''' + @SEARCH + ''''
FROM @INTABLE

INSERT INTO @RESULTTABLE
EXEC (@STAT)

DELETE
FROM @INTABLE

DELETE TOP (1)
FROM @TABLE

SELECT @ROWCOUNT = COUNT(*)
FROM @TABLE
END

SELECT *
FROM @RESULTTABLE

需要指出的是:

不能查找XML

可以使用DISTINCT在最后,去掉重复记录.