Database metadata that isn't really important for NodeBB migration scripts or whatever


  • Discourse touched me in a no-no place

    @PJH said:

    It's just bothering to locate all the tables that have user_id on them (and post_id likewise for if I get round to purging the imported posts I deleted using the UI. )

    One thing I like about Progress is getting that is one line of SQL:

    select _file-name from _file, _field where _field-name = "user_id" and _field._file-recid = recid(_file)

    Having the schema available as tables is really nice. (If SQL databases do that too, cool; I wouldn't know because I don't use 'em enough.)



  • @FrostCat said:

    (If SQL databases do that too, cool; I wouldn't know because I don't use 'em enough.)

    That's what INFORMATION_SCHEMA is for. :)



  • DiscoMarkBBdownCoHTMLde strikes again!



  • I think pretty much all SQL databases do that. Except perhaps SQLite.



  • Not "perhaps SQLite":

    PS C:\Users\Zecc> sqlite3
    SQLite version 3.8.10.2 2015-05-20 18:17:19
    Enter ".help" for usage hints.
    Connected to a transient in-memory database.
    Use ".open FILENAME" to reopen on a persistent database.
    sqlite> create table (id integer primary key autoincrement, derp text);
    sqlite> create table test (id integer primary key autoincrement, derp text);
    sqlite> select * from sqlite_master;
    table|test|test|2|CREATE TABLE test (id integer primary key autoincrement, derp text)
    table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE sqlite_sequence(name,seq)
    sqlite>
    

    TL;DR: sqlite_master



  • Ok, do you know what the word "perhaps" means?

    Per usual, my mistake in using this forum is assuming people can read.



  • @FrostCat said:

    Having the schema available as tables is really nice. (If SQL databases do that too, cool; I wouldn't know because I don't use 'em enough.)

    select table_name from user_tab_columns where column_name='USER_ID';


  • Discourse touched me in a no-no place

    @blakeyrat said:

    I think pretty much all SQL databases do that.

    I assumed so, but I don't know.



  • @blakeyrat said:

    Ok, do you know what the word "perhaps" means?

    Per usual, my mistake in using this forum is assuming people can read.


    Well, excuse me for reinforcing your point then.



  • @FrostCat said:

    If SQL databases do that too, cool;

    In Oracle: select table_name from user_tab_columns where column_name = 'USER_ID'

    Or if you want to look beyond your own schema:
    select owner, table_name from all_tab_columns where column_name = 'USER_ID'

    (partial :hanzo: by @PleegWat)


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.