I needed to find out which columns within all tables in the database, contained a specific string of text. I therefore wrote the script below 1 to do exactly that 🙂
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
CREATE TABLE TempTableColumnResults ( TableName VARCHAR(100), ColumnName VARCHAR(100) ) DECLARE @TableName VARCHAR(100) DECLARE @ColumnName VARCHAR(200) DECLARE @SQL VARCHAR(1000) DECLARE @SearchText VARCHAR(100) SET @SearchText = 'Text to find' DECLARE TableColumns CURSOR FAST_FORWARD FOR SELECT O.name AS TableName, C.name AS ColumnName FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS O ON C.ID = O.ID WHERE O.xtype IN ('U') AND C.xtype NOT IN (34,241,98) --Exclude image, sql_variant, xml data types ORDER BY O.name, C.ColOrder OPEN TableColumns FETCH NEXT FROM TableColumns INTO @TableName, @ColumnName SET NOCOUNT ON WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'INSERT INTO TempTableColumnResults (TableName, ColumnName) ' SET @SQL = @SQL + 'SELECT ' + CHAR(39) + @TableName + CHAR(39) + ', ' SET @SQL = @SQL + CHAR(39) + @ColumnName + CHAR(39) + ' ' SET @SQL = @SQL + 'FROM ' + QUOTENAME(@TableName) + ' ' SET @SQL = @SQL + 'WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ' SET @SQL = @SQL + CHAR(39) + '%' + @SearchText + '%' + CHAR(39) + ' ' EXEC (@SQL) FETCH NEXT FROM TableColumns INTO @TableName, @ColumnName END CLOSE TableColumns DEALLOCATE TableColumns SELECT DISTINCT TableName, ColumnName FROM TempTableColumnResults DROP TABLE TempTableColumnResults |
- Please ensure you test any script taken from my website on a test/development machine, before running on a production server. ↩
