-- input parameters (guessing on type for @value):DECLARE @schema SYSNAME = N'dbo', @table SYSNAME = N'z', @value VARCHAr(64) = '75';-- now, inside the procedure body:DECLARE @sql NVARCHAr(MAX) = N'SELECt ''cols:'' + STUFF(''''';SELECT @sql += N' + CASE WHEN EXISTS (SELECT 1 FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' WHERe TRY_ConVERT(VARCHAr(64), ' + QUOTENAME(c.name) + ') = @value) THEN '', ' + c.name + ''' ELSE '''' END'FROM sys.tables AS tINNER JOIN sys.columns AS cON t.[object_id] = c.[object_id]INNER JOIN sys.schemas AS sON t.[schema_id] = s.[schema_id]WHERe t.name = @table AND s.name = @schema;SET @sql += N', 1, 1, '''');'PRINT @sql;--EXEC sp_executesql @sql, N'@value VARCHAr(64)', @value;当您对输出感到满意时，请取消注释
EXEC。
因此，让我们考虑一个简单的表：
CREATE TABLE dbo.floob( a INT, b VARCHAr(32), c VARBINARY(22), d DATE, e DATETIME, f ROWVERSION);INSERT dbo.floob(a,b,c,d,e) VALUES( 75, 'foo', 0x00, GETDATE(), GETDATE()),( 21, '75', 0x00, GETDATE(), GETDATE());
现在，基于上述代码的存储过程：
CREATE PROCEDURE dbo.FindStringInAnyColumn @schema SYSNAME = N'dbo', @table SYSNAME, @value VARCHAr(64)ASBEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAr(MAX) = N'SELECt ''cols:'' + STUFF('''''; SELECT @sql += N' + CASE WHEN EXISTS (SELECT 1 FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' WHERe TRY_ConVERT(VARCHAr(64), ' + QUOTENAME(c.name) + ') = @value) THEN '', ' + c.name + ''' ELSE '''' END' FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.[object_id] = c.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERe t.name = @table AND s.name = @schema; SET @sql += N', 1, 1, '''');' EXEC sp_executesql @sql, N'@value VARCHAr(64)', @value;ENDGO用法示例：
EXEC dbo.FindStringInAnyColumn @table = N'floob', @value = '75';
输出：
Cols: a, b
