Created
November 18, 2020 21:28
-
-
Save charliewynn/e9e87d0c96e7c86d74f989d8d5770ca6 to your computer and use it in GitHub Desktop.
Sql find value
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
| --Run using | |
| -- exec SearchAllTables 'my Search' | |
| --maybe edit the order By at the very bottom of this file | |
| -- drop proc SearchAllTables | |
| CREATE PROC SearchAllTables | |
| ( | |
| @SearchStr nvarchar(100) | |
| ) | |
| AS | |
| BEGIN | |
| -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. | |
| -- Purpose: To search all columns of all tables for a given search string | |
| -- Written by: Narayana Vyas Kondreddi | |
| -- Site: http://vyaskn.tripod.com | |
| -- Tested on: SQL Server 7.0 and SQL Server 2000 | |
| -- Date modified: 28th July 2002 22:50 GMT | |
| DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) | |
| SET NOCOUNT ON | |
| DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) | |
| SET @TableName = '' | |
| SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') | |
| WHILE @TableName IS NOT NULL | |
| BEGIN | |
| SET @ColumnName = '' | |
| SET @TableName = | |
| ( | |
| SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) | |
| FROM INFORMATION_SCHEMA.TABLES | |
| WHERE TABLE_TYPE = 'BASE TABLE' | |
| AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName | |
| AND OBJECTPROPERTY( | |
| OBJECT_ID( | |
| QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) | |
| ), 'IsMSShipped' | |
| ) = 0 | |
| ) | |
| WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) | |
| BEGIN | |
| SET @ColumnName = | |
| ( | |
| SELECT MIN(QUOTENAME(COLUMN_NAME)) | |
| FROM INFORMATION_SCHEMA.COLUMNS | |
| WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) | |
| AND TABLE_NAME = PARSENAME(@TableName, 1) | |
| AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') | |
| AND QUOTENAME(COLUMN_NAME) > @ColumnName | |
| ) | |
| IF @ColumnName IS NOT NULL | |
| BEGIN | |
| INSERT INTO @Results | |
| EXEC | |
| ( | |
| 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) | |
| FROM ' + @TableName + ' (NOLOCK) ' + | |
| ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 | |
| ) | |
| END | |
| END | |
| END | |
| SELECT ColumnName, ColumnValue FROM @Results order by len(ColumnValue) | |
| END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment