5 Replies Latest reply on Apr 10, 2017 10:53 AM by Doug Castell

    Unpopulated Fields

    TPorte Apprentice

      Is there a SQL query to show which fields in Goldmine have no data in them?  I would like to exclude the unpopulated fields from an export.  Thanks!

        • 1. Re: Unpopulated Fields
          John Neighbors SSMMVPGroup

          Terry,

           

          GoldMine itself does not have anything like this (out of the box, pre-made...you could always write per-field queries).  However, knowing that you all have GoldBox, I thought I'd point you to a great feature that it offers that should address your need:

           

          1. Open Goldbox.

           

          2. On the File Menu, choose Contact File VERIFIER.

           

           

          3. On the screen presented, select options A and C (B is always checked with A).

           

          4. Click the GO button at the top right.

           

          5. Wait for it to churn and provide results.

           

          NOTE:  In order to properly document this, I actually ran all this just now on your server.  To save you a little time, I saved the results file (a simple TXT file) on the Admninistrator's Desktop on your server.  It's named:  GX9_Contact_File_VERIFIER.txt

           

          Within this results file, you will see a "(Empty)" notation next to any fields that are EMPTY on ALL records.

           

          Hope this feedback helps.

          1 of 1 people found this helpful
          • 2. Re: Unpopulated Fields
            TPorte Apprentice

            John,

             

            Thanks so much!  This is valuable feedback.  Will check out that file.

            • 3. Re: Unpopulated Fields
              TFutre Rookie

              Something like the following should work.

               

               

              SELECT FIELDNAME

              FROM

               

              (SELECT 'COMPANY' AS FIELDNAME,COUNT(*) AS RECORDS FROM GOLDMINE.DBO.CONTACT1 WHERE LEN(COMPANY) >1

              UNION

              SELECT 'ADDRESS1', COUNT(*) FROM GOLDMINE.DBO.CONTACT1 WHERE LEN(ADDRESS1) > 1

              UNION

              SELECT 'ADDRESS2', COUNT(*) FROM GOLDMINE.DBO.CONTACT1 WHERE LEN(ADDRESS2) > 1

              UNION

              SELECT 'ADDRESS3', COUNT(*) FROM GOLDMINE.DBO.CONTACT1 WHERE LEN(ADDRESS3) > 1

              UNION

              SELECT 'DEAR',COUNT(*) FROM GOLDMINE.DBO.CONTACT1 WHERE LEN(DEAR) >1)GM

               

               

              WHERE RECORDS = 0

               

               

              /*You'll have to add to the union query to examine all fields in your table

              UNION

              select fieldname,count(*) from goldmine.dbo.tablename where len(fieldname)> 1

              union

              select fieldname,count(*) from goldmine.dbo.tablename where len(fieldname)> 1

              repeat...

              */

              If your field is likely to have valid single-character entries you'll have to tinker with the query

              • 4. Re: Unpopulated Fields
                LFletc Apprentice

                Hi

                 

                Here is a SQL stored procedure that should help..

                 

                USE [GoldMine]

                GO

                 

                /****** Object:  StoredProcedure [dbo].[usp_FieldValueCount]    ******/

                SET ANSI_NULLS ON

                GO

                 

                SET QUOTED_IDENTIFIER OFF

                GO

                 

                CREATE PROCEDURE [dbo].[usp_FieldValueCount] (@Tablename varchar(255), @Fieldname varchar(255) = '%') AS

                BEGIN

                SET NOCOUNT ON

                CREATE TABLE ##RESULTS([FIELDNAME] varchar(12) NULL, [VALUES] int)

                 

                DECLARE @Q CHAR(1)

                DECLARE @CMD VARCHAR(255)

                DECLARE @RESULT INT

                DECLARE @TABLECOUNT VARCHAR(10)

                DECLARE @FIELDTYPE INT

                SET @Q = CHAR(39)

                SET @CMD = 'SELECT SC.NAME, SC.XTYPE INTO ##FIELDNAMES FROM SYSOBJECTS SO, SYSCOLUMNS SC WHERE SO.ID = SC.ID AND SO.NAME = ' + @Q + @TABLENAME + @Q + ' AND SC.NAME LIKE ' + @Q + @FIELDNAME + @Q + ' AND SC.XTYPE <> 35 ORDER BY COLID'

                EXEC (@CMD)

                SET @CMD = 'SELECT COUNT(*) [TABLECOUNT] INTO ##TABLECOUNT FROM ' + @TABLENAME

                EXEC (@CMD)

                SELECT @TABLECOUNT = TABLECOUNT FROM ##TABLECOUNT

                DECLARE CUR_FIELDNAME CURSOR

                KEYSET

                FOR

                SELECT NAME, XTYPE FROM ##FIELDNAMES

                OPEN CUR_FIELDNAME

                FETCH NEXT FROM CUR_FIELDNAME INTO @FIELDNAME, @FIELDTYPE

                WHILE (@@FETCH_STATUS <> -1)

                BEGIN

                IF (@@FETCH_STATUS <> -2)

                BEGIN

                  SET @CMD = 'INSERT INTO ##RESULTS SELECT ' + @Q + @FIELDNAME + @Q + ' [FIELDNAME], ' + @TABLECOUNT + '-COUNT(*) [VALUES] FROM ' + @TABLENAME + ' WHERE ISNULL(' + @FIELDNAME + ',' + @Q + @Q + ') = ' + @Q + @Q

                  EXEC (@CMD)

                END

                FETCH NEXT FROM CUR_FIELDNAME INTO @FIELDNAME, @FIELDTYPE

                END

                CLOSE CUR_FIELDNAME

                DEALLOCATE CUR_FIELDNAME

                SET NOCOUNT OFF

                SELECT * FROM ##RESULTS order by 2

                DROP TABLE ##FIELDNAMES

                DROP TABLE ##RESULTS

                DROP TABLE ##TABLECOUNT

                END

                 

                GO

                 

                 

                Here is an example to run the procedure.  The field name variable is set to only check field beginning with U, just a % will do all fields in the table

                 

                USE [GoldMine]

                GO

                 

                EXEC [dbo].[usp_FieldValueCount]

                  @Tablename = N'CONTACT2',

                  @Fieldname = N'U%'

                 

                GO

                1 of 1 people found this helpful
                • 5. Re: Unpopulated Fields
                  Doug Castell Expert

                  This one will show you the unused CONTACT2 fields:

                   

                   

                  declare @tempTable table

                  (

                  TableName nvarchar(256),

                   

                  ColumnName sysname,

                   

                  NotNullCnt bigint

                  );

                  declare @sql nvarchar(4000),

                   

                  @tableName nvarchar(256),

                   

                  @columnName sysname,

                   

                  @cnt bigint

                   

                  declare columnCursor cursor for

                   

                  select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'YES' AND table_name LIKE 'CONTACT2' and column_name like 'U%';

                   

                  open columnCursor;

                   

                  fetch next from columnCursor into @tableName, @columnName;

                   

                  while @@FETCH_STATUS = 0

                  begin

                  set @sql = 'select @cnt = COUNT(*) from [' + @tableName + '] WHERE [' + @columnName + '] IS NOT null AND [' + @columnName + '] NOT LIKE '''' AND [' + @columnName + '] NOT LIKE ''0''';

                  -- print @sql; --uncomment for debugging

                  exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

                   

                  insert into @tempTable select @tableName, @columnName, @cnt;

                   

                  fetch next from columnCursor into @tableName, @columnName;

                  end

                  close columnCursor;

                   

                  deallocate columnCursor;

                   

                  select * from @tempTable where NotNullCnt = 0;

                  1 of 1 people found this helpful