以下语句类似查找中的 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在最后,去掉重复记录.