Most administrators can find their way around the inside of a GoldMine database without too much trouble and, usually, it’s fairly clear where things live.

Sometimes though, and particularly with new features, it can be hard to track down the table and field that holds a particular type of information.

What we need is something that can search through every field in the database and then returns the tables and fields where it finds a match. This is quite different from GoldMine’s universal search, in that it’s not the data itself that’s the important thing here but its specific position in the data structure.

I found what I needed in order to accomplish this here: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

It’s a stored procedure, which the code below will set up in your database when run from Management Studio. Make sure that you set the database name at the top.

Thereafter, in order to use it, just open a query window in Management Studio and type:


EXEC spSearchAllTables 'some data to find'.


Of course, it would be nice to be able to call this from GoldMine’s own SQL query window, but this is currently not possible. In order to do this, we would have to use a function, not a procedure, so that we could say ‘select..’ instead of ‘exec…’; unfortunately, though, functions can’t execute dynamic SQL statements, which is what this procedure relies upon.

Here it is:


USE GoldMine

go

if exists (select * from dbo.sysobjects where id = object_id(N'[spSearchAllTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[spSearchAllTables]
GO

CREATE PROC spSearchAllTables
(
@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 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
END