这将起作用:
DECLARE @MyValue NVarChar(4000) = 'something';
SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
sys.tables T ON S.schema_id = T.schema_id;
WHILE (EXISTS (SELECT * FROM #T)) BEGIN
DECLARE @sql NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
DECLARE @TableName NVarChar(1000) = (
SELECT TOP 1 SchemaName + '.' + TableName FROM #T
);
SELECT @sql = REPLACE(@sql, '$$TableName', @TableName);
DECLARE @Cols NVarChar(4000) = '';
SELECT
@Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
FROM sys.columns C
WHERE C.object_id = OBJECT_ID(@TableName);
SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
SELECT @sql = @sql + @Cols;
EXECUTE(@sql);
DELETE FROM #T
WHERE SchemaName + '.' + TableName = @TableName;
END;
DROP TABLE #T;
不过,有几点警告。首先, 。所有值都将被nvarchar
简单地转换,以便可以比较它们而不会出错。您可能会遇到一些问题,例如datetime
无法按预期方式转换值,因此在应有的值不匹配时(假否定)。
使用WHERE (0 = 1)
可以OR
简化子句的构建。如果没有匹配项,则不会返回任何行。