SharePoint 2007 / Project Server GUID Error
SQL Query to Search All Tables in Database At One Time

SharePoint and all those Guids... Ever get an event log error message that only revealed a GUID based field? Well, now you can search your entire database to find all instances of the GUID. This will dramatically save you time in tracing down that error!
Just type in your database name in line 5 and the GUID you are looking for in line 73. In addition, this query can be used to search for any type of data. Just replace the data type 'uniqueidentifier' in line 52 with the data type you are looking for. Examples of other data types are listed in lines 49 and 50.

-- This script will search the entire database for a value you specify in EXEC SearchAllTables line at end of proc.
-- Then display the results in a 'readable' form "Database.TABLE.COLUMN | Value"
-- This will create the 'temporary' procedure used for this search

USE MSPContent01DB     -- change to database name you need
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC 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)

-- change data type to type you are searching for 
-- search all char field is ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')

					AND	DATA_TYPE IN ('uniqueidentifier') 
					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
END
GO

EXEC SearchAllTables 'fc5a6171-f3d0-4e2b-9b02-7d2d7b501e7d' -- Change the value in the single quotes '****' to the value you are looking for in all tables...
GO

-- This will delete the 'created' procedure used for searching...
DROP PROC SearchAllTables
GO

Permalink | Comments (0) | Post RSSRSS comment feed