SQL Learning Center - GoldMine » Blog Archive » Create table with GoldMine Field Usage Counts

Create table with GoldMine Field Usage Counts

by Doug Steinschneider @ 1:46 pm. Filed under MSSQL Query Analyzer

Description:
Run this only in Query Analzyer to create a table called ‘gm_userdef_usage_count’ which holds a row for each field and a count for how many times it has been used.

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'gm_userdef_usage_count' AND type = 'U')
DROP TABLE gm_userdef_usage_count
CREATE TABLE gm_userdef_usage_count
( FieldName VARCHAR(50),
RecCount INT )
--Declare cursor
DECLARE curs CURSOR
GLOBAL
SCROLL
STATIC
FOR
SELECT Field_Name,Field_Type FROM contudef WHERE dbfname ='CONTACT2' and field_name like 'U%'
go
--Declare variables
DECLARE @fldname VARCHAR(50)
DECLARE @fldtype VARCHAR(1)
DECLARE @temp VARCHAR(150)
DECLARE @MAX INT
DECLARE @counter INT
SET @counter = 1
SELECT @MAX = COUNT(field_name) FROM contudef WHERE dbfname ='CONTACT2' and field_name like 'U%'
--Open cursor and fetch record
OPEN curs
FETCH NEXT FROM curs INTO @fldname,@fldtype
--loop through them all
WHILE @counter < = @MAX
BEGIN
SELECT @counter = @counter+1
--print @fldname
IF @fldtype = 'C' or @fldtype = 'D'
SET @temp = 'Insert into gm_userdef_usage_count Select ' + CHAR(39) + @fldname + CHAR(39) + ', count(accountno) from contact2 where ' + @fldname + ' is not null '
ELSE
SET @temp = 'Insert into gm_userdef_usage_count Select ' + CHAR(39) + @fldname + CHAR(39) + ', count(accountno) from contact2 where ' + @fldname + ' = 0 '
EXEC(@temp)
FETCH NEXT FROM curs INTO @fldname,@fldtype
END
CLOSE curs
DEALLOCATE curs