Try this-- Prepare user supplied parameter valueDECLARE @SearchWord NVARCHAR(100) = 'A Phrase To Search For';-- Stage columnsDECLARE @Columns TABLE ( ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, TableSchema SYSNAME NOT NULL, TableName SYSNAME NOT NULL, ColumnList NVARCHAR(MAX) NOT NULL );INSERT @Columns ( TableSchema, TableName, ColumnList )SELECT t.TABLE_SCHEMA AS TableSchema, t.TABLE_NAME AS TableName, STUFF(c.ColumnList, 1, 1, '') AS ColumnListFROM ( SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ) AS tCROSS APPLY ( SELECT ',' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS AS x WHERE x.TABLE_SCHEMA = t.TABLE_SCHEMA AND x.TABLE_NAME = t.TABLE_NAME AND x.DATA_TYPE IN ('TEXT', 'NTEXT', 'VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR') FOR XML PATH('') ) AS c(ColumnList)WHERE c.ColumnList > ''ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;DECLARE @CurrID INT = 1, @StopID INT = (SELECT MAX(ID) FROM @Columns), @SQL NVARCHAR(MAX);WHILE @CurrID <= @StopID BEGIN SELECT @SQL = N'SELECT N' + QUOTENAME(TableSchema + N'.' + Tablename, N'''') + N' AS TableName, *' + N' FROM ' + QUOTENAME(TableSchema) + N'.' + QUOTENAME(TableName) + N' WHERE N' + QUOTENAME(@SearchWord, N'''') + N' IN (' + ColumnList + N');' FROM @Columns WHERE ID = @CurrID; PRINT @SQL; EXEC (@SQL); SET @CurrID += 1; END;
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA