cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

Check if table exist on sql db

geoff1
Level II

Hello,

 

I am running some jmpquery on different SQL DB. Because some DB do not have the same tables,  I getting the error:

Tables (public.table1(t1)) referenced in the SQL Query were not found in the database{1} in access or evaluation of 'Table' , Table( "table1", Schema( "public" ), Alias( "t1" ) ) /*###*/

 

How could I check if a table exists on a SQL DB before running a query ? 

 

Thanks

Geof

4 REPLIES 4
geoff1
Level II


Re: Check if table exist on sql db

I found using the custom SQL below, it returns 0 or 1 if it exists.

 

Names Default To Here( 1 );
obj = New SQL Query(
	Connection( "ODBC:DSN=xxxx" ),
	Custom SQL(
		"SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'table1'
);"
	)
);
obj << run;
geoff1
Level II

Re: Check if table exist on sql db

it returns a table, how to get an argument? boolean ?

Georg
Level VII

Re: Check if table exist on sql db

argument = N Rows(dt);

returns the number of rows of the table. If there is no row, the table was not there

Georg
pmroz
Super User

Re: Check if table exist on sql db

If this is SQL Server something like this (untested) should work:

Names Default To Here( 1 );

schema_name = "PUBLIC";
table_name  = "TABLE1";

sql = evalinsert(
"SELECT COUNT(*) N
   FROM information_schema.tables
  WHERE table_schema = '^schema_name^'
    AND table_name = '^table_name^'");

dbc = create database connection("ODBC:DSN=xxxx");
dt = execute sql(dbc, sql, invisible);
close database connection(dbc);
ntables = dt:N[1];
if (ntables,
	print("Table " || table_name || " exists");
	,
	print("Table " || table_name || " does not exist");
);close(dt, nosave);