my code stock.com

dmiska

Search for any value across DB
by dmiska

Search for any value across database.

Snippet options

Download: Download snippet as search-for-any-value-across-db.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

DECLARE @Text VARCHAR(1024) = '6CBD6788-E5F5-4B1D-A23B-2F27CDF9AC22', 
		@ExactMatchOnly BIT = 0, 
		@ShowNotFoundMsg BIT = 1

DECLARE @ColumnsToSearch TABLE (
	LineId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
	SchemaName SYSNAME NOT NULL, 
	TableName SYSNAME NOT NULL, 
	ColumnName SYSNAME NOT NULL
)

--Find all string-based columns large enough to hold the text being searched
INSERT INTO @ColumnsToSearch (SchemaName, TableName, ColumnName)
	SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
		FROM INFORMATION_SCHEMA.COLUMNS AS C 
			INNER JOIN INFORMATION_SCHEMA.TABLES AS T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME
	WHERE T.TABLE_TYPE = 'BASE TABLE'
		AND (
				(C.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') AND C.CHARACTER_MAXIMUM_LENGTH >= LEN(@Text))
				OR 
				(C.DATA_TYPE IN ('uniqueidentifier'))
			)

-- Search every column
DECLARE @LineId INT, @FullyQualifiedTableName NVARCHAR(256), @ColumnToSearch SYSNAME, @Sql NVARCHAR(1024)

SELECT @LineId = LineId, @FullyQualifiedTableName = '[' + SchemaName + '].[' + TableName + ']', @ColumnToSearch = '[' + ColumnName + ']' 
	FROM @ColumnsToSearch

WHILE NOT @LineId IS NULL
BEGIN
  -- Create the base query against the table
  SET @Sql = 'SELECT ''' + @FullyQualifiedTableName + '.' + @ColumnToSearch + ''' AS [Table_Column], * FROM ' 
        + @FullyQualifiedTableName + ' WHERE ' + @ColumnToSearch + 
			CASE @ExactMatchOnly
				WHEN 1 THEN '=''' + @Text + ''''
				ELSE ' LIKE ''%' + @Text + '%'''
			END

        -- Only return rows that actually contain the search term otherwise write out a message saying the table/column did not have the criteria
        EXEC ('IF EXISTS (' + @Sql + ') ' + @Sql + ' ELSE IF ' + @ShowNotFoundMsg + ' = 1 PRINT ''' + @FullyQualifiedTableName + '.' + @ColumnToSearch + ' - no matches''')
        DELETE @ColumnsToSearch WHERE [email protected]
        SET @LineId = NULL
        SELECT @LineId = LineId, @FullyQualifiedTableName = '[' + SchemaName + '].[' + TableName + ']', @ColumnToSearch = '[' + ColumnName + ']' FROM @ColumnsToSearch
END

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.