Created
August 9, 2023 20:00
-
-
Save charliewynn/5675521a4121b3dd290ffa00e0ad6f34 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @searchValue NVARCHAR(255) = '1856' -- Text or Number (text is converted in number if necessary) | |
| IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL | |
| BEGIN | |
| DROP TABLE #SearchResults | |
| END | |
| CREATE TABLE #SearchResults ( | |
| TableName NVARCHAR(128), | |
| ColumnName NVARCHAR(128), | |
| Value NVARCHAR(MAX) | |
| ) | |
| DECLARE @tableName NVARCHAR(128) | |
| DECLARE @columnName NVARCHAR(128) | |
| DECLARE @query NVARCHAR(MAX) | |
| DECLARE @type NVARCHAR(50) | |
| DECLARE db_cursor CURSOR FOR | |
| SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS Type | |
| FROM sys.tables t | |
| JOIN sys.columns c ON t.object_id = c.object_id | |
| JOIN sys.types ty ON c.user_type_id = ty.user_type_id | |
| WHERE ty.name IN ('nvarchar', 'varchar', 'int', 'bigint', 'smallint', 'tinyint') | |
| OPEN db_cursor | |
| FETCH NEXT FROM db_cursor INTO @tableName, @columnName, @type | |
| WHILE @@FETCH_STATUS = 0 | |
| BEGIN | |
| IF @type IN ('nvarchar', 'varchar') | |
| BEGIN | |
| SET @query = 'INSERT INTO #SearchResults (TableName, ColumnName, Value) | |
| SELECT DISTINCT ''' + @tableName + ''', ''' + @columnName + ''', ' + 'CONVERT(NVARCHAR(MAX), ' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' LIKE ''%' + @searchValue + '%''' | |
| END | |
| ELSE IF @type IN ('int', 'bigint', 'smallint', 'tinyint') | |
| BEGIN | |
| SET @query = 'INSERT INTO #SearchResults (TableName, ColumnName, Value) | |
| SELECT DISTINCT ''' + @tableName + ''', ''' + @columnName + ''', ' + 'CONVERT(NVARCHAR(MAX), ' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' = TRY_CAST(' + QUOTENAME(@searchValue, '''') + ' AS ' + @type + ')' | |
| END | |
| EXEC sp_executesql @query | |
| FETCH NEXT FROM db_cursor INTO @tableName, @columnName, @type | |
| END | |
| CLOSE db_cursor | |
| DEALLOCATE db_cursor | |
| SELECT * FROM #SearchResults | |
| DROP TABLE #SearchResults |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment