Searching SQL - Finding that one record

Sometimes you need to find more information about an item and all you have is a value or perhaps a GUID from an error.

Or perhaps your building a report and need to find where a column is stored in a SQL database.

Searching for a GUID or a value within a field anywhere in a database

The following is a self contained script, leaving no changes behind to the Dynamics database.

USE [BSMT_MSCRM] GO /********* 3 Parts, Create Stored procedure, Search for a GUID, Drop Stored procedure *******/

/****** Create StoredProcedure [dbo].[SearchAllTables] Script Date: 11/10/2018 09:36:38 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROC [dbo].[SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN

CREATE TABLE #Results (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 QUOTENAME(COLUMN_NAME) > @ColumnName )

IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2 ) END END END

SELECT ColumnName, ColumnValue FROM #Results END GO

/**** Search Part ****/ DECLARE @return_value int

EXEC @return_value = [dbo].[SearchAllTables] /**** Search String Value ****/ @SearchStr = N'c83d35a1-67a0-e811-80ea-005056301477' /**** Search String Value ****/ SELECT 'Return Value' = @return_value

GO

/****** Drop Stored Procedure ******/ DROP PROCEDURE [dbo].[SearchAllTables] GO

Searching for a column in a database

SELECT c.name AS 'ColumnName' ,t.name AS 'TableName' FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE 'ObjectId' ORDER BY TableName ,ColumnName;