Created
June 12, 2024 06:43
-
-
Save FARUK-YILDIRIM/d3ea4f272372e39e9a0967692dd637e6 to your computer and use it in GitHub Desktop.
This script dynamically searches for a specific value in columns of character and text data types across all tables in the database. It then returns the table and column names where the value is found.
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 @search_value NVARCHAR(100) = '46CF7YQFBUU1159'; | |
| DECLARE @query NVARCHAR(MAX) = ''; | |
| SELECT @query = @query + | |
| 'SELECT ''' + t.name + ''' AS [Table], ''' + c.name + ''' AS [Column] FROM ' + QUOTENAME(t.name) + ' WHERE CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)) = ''' + @search_value + ''' UNION ALL ' | |
| FROM | |
| sys.columns c | |
| INNER JOIN | |
| sys.tables t ON c.object_id = t.object_id | |
| WHERE | |
| c.system_type_id IN ( | |
| SELECT system_type_id | |
| FROM | |
| sys.types | |
| WHERE | |
| name LIKE '%char%' | |
| OR name LIKE '%text%' | |
| ); | |
| SET @query = LEFT(@query, LEN(@query) - LEN(' UNION ALL ')); | |
| EXEC sp_executesql @query; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment