A personal repository of random information in compensation for a fatigued biological computer
Breaded IT » MS Access / SQL Server reminders » SQL Server 2000-2005: Find which table/field holds a specific char snippet
I know a reference valus is in a data table somewhere.. but which table/olumn:
DECLARE @value VARCHAR(64)DECLARE @sql VARCHAR(1024)DECLARE @table VARCHAR(64)DECLARE @column VARCHAR(64)SET @value = 'sound recording' - target value substringCREATE TABLE #t (tablename VARCHAR(64),columnname VARCHAR(64))DECLARE TABLES CURSORFORSELECT o.name, c.nameFROM syscolumns cINNER JOIN sysobjects o ON c.id = o.idWHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)ORDER BY o.name, c.nameOPEN TABLESFETCH NEXT FROM TABLESINTO @table, @columnWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '--SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') 'SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') 'SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''SET @sql = @sql + @column + ''')'EXEC(@sql)FETCH NEXT FROM TABLESINTO @table, @columnENDCLOSE TABLESDEALLOCATE TABLESSELECT *FROM #tDROP TABLE #t
xx