Application Note for QuNect ODBC for QuickBase
Create a table with all the fields in your Quickbase tables in SQL Server
The following stored procedure will create a table in SQL Server called qunect_fields. It will populate this table with one row for every field in all your Quickbase tables. If qunect_fields already exists it will drop (delete) the table and recreate it.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Claude von Roesgen -- Create date: 3/5/2019 -- Description: Create a table with all the fields in your Quickbase tables -- ============================================= -- This stored procedure depends on the existence of a linked server called -- Quickbase. To create this linked server run the following SQL: -- -- EXEC sp_addlinkedserver -- @server = 'QuickBase', -- @provider = 'MSDASQL', -- @srvproduct = 'QuNect', -- @provstr = 'DRIVER={QuNect ODBC for QuickBase};UID=YourQuickBaseEmailOrScreenName;PWD=YourPassword' -- GO -- -- After you have your linked server in place you're ready to run the stored -- procedure below. CREATE PROCEDURE [dbo].[fieldsForAllQuickBaseTables] AS BEGIN SET NOCOUNT ON; if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_tables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[qunect_tables] if exists (select * from sysobjects where id = object_id(N'[dbo].[qunect_fields]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[qunect_fields] CREATE TABLE [dbo].[qunect_tables] ( [TABLE_QUALIFIER] [nvarchar] (128) NULL , [TABLE_OWNER] [nvarchar] (128) NULL , [TABLE_NAME] [nvarchar] (128) NULL , [TABLE_TYPE] [nvarchar] (128) NULL , [REMARKS] [varchar] (254) NULL ) ON [PRIMARY] INSERT qunect_tables EXEC sp_tables_ex quickbase DECLARE @dbid nvarchar(128) DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM qunect_tables OPEN table_cursor FETCH NEXT FROM table_cursor INTO @dbid WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN PRINT 'Getting columns for ' + @dbid BEGIN BEGIN TRY EXEC('SELECT * INTO [qunect_fields] FROM OPENQUERY(QUICKBASE, ''select '''''+@dbid+''''' as dbid, [label] ,[COLUMN_NAME] ,[fid] ,[base_type] ,[field_type] ,[mode] ,[role] ,[abbreviate] ,[allow_new_choices] ,[allowHTML] ,[appears_as] ,[appears_by_default] ,[append_only] ,[auto_save] ,[blank_is_zero] ,[bold] ,[carrychoices] ,[choices] ,[comma_start] ,[currency_format] ,[currency_symbol] ,[decimal_places] ,[default_kind] ,[default_today] ,[default_value] ,[display_dow] ,[display_graphic] ,[display_month] ,[display_relative] ,[display_time] ,[display_user] ,[display_zone] ,[does_average] ,[does_total] ,[doesdatacopy] ,[fieldhelp] ,[find_enabled] ,[foreignkey] ,[format] ,[formula] ,[hours24] ,[mastag] ,[max_versions] ,[maxlength] ,[nowrap] ,[num_lines] ,[numberfmt] ,[parentFieldID] ,[required] ,[see_versions] ,[sort_as_given] ,[isunique] ,[use_new_window] ,[width] ,[iskey] ,[lusfid] ,[lutfid] ,[snapfid] from "'+@dbid+'~fields"'')') ALTER TABLE [qunect_fields] ALTER COLUMN dbid nvarchar(256) ; END TRY BEGIN CATCH EXEC('INSERT INTO [qunect_fields] SELECT * FROM OPENQUERY(QUICKBASE, ''select '''''+@dbid+''''' as dbid, [label] ,[COLUMN_NAME] ,[fid] ,[base_type] ,[field_type] ,[mode] ,[role] ,[abbreviate] ,[allow_new_choices] ,[allowHTML] ,[appears_as] ,[appears_by_default] ,[append_only] ,[auto_save] ,[blank_is_zero] ,[bold] ,[carrychoices] ,[choices] ,[comma_start] ,[currency_format] ,[currency_symbol] ,[decimal_places] ,[default_kind] ,[default_today] ,[default_value] ,[display_dow] ,[display_graphic] ,[display_month] ,[display_relative] ,[display_time] ,[display_user] ,[display_zone] ,[does_average] ,[does_total] ,[doesdatacopy] ,[fieldhelp] ,[find_enabled] ,[foreignkey] ,[format] ,[formula] ,[hours24] ,[mastag] ,[max_versions] ,[maxlength] ,[nowrap] ,[num_lines] ,[numberfmt] ,[parentFieldID] ,[required] ,[see_versions] ,[sort_as_given] ,[isunique] ,[use_new_window] ,[width] ,[iskey] ,[lusfid] ,[lutfid] ,[snapfid] from "'+@dbid+'~fields"'')') END CATCH END END FETCH NEXT FROM table_cursor INTO @dbid END DEALLOCATE table_cursor END