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
/* 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;

Thank you to the community on Stackoverflow

#Dynamics365 #Tools #Search #script #DynamicsCRM #sharepoint #projectserver #Reports #SQLReportingServices #SSRS