Виж това за идеи:
IF OBJECT_ID('usp_SearchTables') IS NOT NULL DROP PROC usp_SearchTables
GO
-- exec dbo.usp_SearchTables 'wqw%'
CREATE PROC usp_SearchTables (
@SearchText VARCHAR(1000)
) AS
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000)
, @ParamList NVARCHAR(4000)
, @TableName SYSNAME
SET @ParamList = '@SearchText VARCHAR(1000)'
--- prepare temp table w/ columns/tables to be processed
SELECT c.COLUMN_NAME, c.TABLE_NAME
INTO #TmpCol
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE IN ('BASE TABLE') -- 'VIEW'
AND DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR') -- 'TEXT', 'NTEXT'
--- loop columns/tables
WHILE EXISTS (SELECT * FROM #TmpCol)
BEGIN
SELECT TOP 1 @TableName = TABLE_NAME
FROM #TmpCol
SET @SQL = NULL
--- construct WHERE clause
SELECT @SQL = CASE WHEN @SQL IS NULL THEN '' ELSE @SQL + ' OR ' END
+ QUOTENAME(COLUMN_NAME) + ' LIKE @SearchText'
FROM #TmpCol
WHERE TABLE_NAME = @TableName
--- construct IF EXISTS statement
SET @SQL = '
IF EXISTS ( SELECT *
FROM ' + QUOTENAME(@TableName) + '
WHERE ' + @SQL + ')
RAISERROR (''Found in ' + REPLACE(@TableName, '''', '''''') + ''', 10, 1) WITH NOWAIT'
-- PRINT @SQL
EXEC dbo.sp_executesql @SQL, @ParamList, @SearchText
DELETE #TmpCol
WHERE TABLE_NAME = @TableName
END
GO
cheers,
</wqw>