Using PROC SQL to get the schema of a MySQL database

It's Friday, and on SAS Voices they are posting fun stuff about dogs who work at SAS. I'm posting about PROC SQL and MySQL. You tell me - which of us knows how to ring in the weekend with style?

I've been working with MySQL data sources lately, and SAS/ACCESS to MySQL makes it easy to do my work in SAS. I can assign a SAS library and then work with the tables and columns almost exactly as if they lived in native SAS data sets. The database columns are automatically visible to my SAS program with SAS-style attributes, such as SAS formats and lengths.

But when I get started with a new database that I'm not familiar with, I'd like to know how MySQL sees that data, including the database-centric attributes. That information is available in the information_schema structure within the MySQL instance. (Note: other databases support the information_schema, so this program can go beyond just MySQL.)

/* must be run where SAS/ACCESS to MySQL available */
%let database=dbname; /* name of the MySQL database */
%let server = mysql.host.domain.com; /* your MySQL node */

proc sql;
connect to mysql as source
(
DATABASE=&database
SERVER=&server
PORT=3306 /* default port, change if needed */
USER=your_user_id
PASSWORD=secretPW
);
create table work.schematables as
select * from connection to source
(select * from
information_schema.tables
where table_schema="&database");

create table work.schemacolumns as
select * from connection to source
(select * from
information_schema.columns
where table_schema="&database");
disconnect from source;

quit;

After plugging my database information and credentials into the above program, and then running it, I receive two output tables. One table (WORK.SCHEMATABLES) shows me all of the database table attributes, such as the table row count, create/update times, index length, and more. I find the other table (WORK.SCHEMACOLUMNS) to be more interesting. It shows me the column names, intrinsic type (bigint, varchar, datetime, and so on), length, whether it's NULLible, and more.

Oh, and if you're a bit queasy about putting your database credentials into a SAS program like this, check out these five strategies to eliminate passwords from your SAS programs.