2 Replies Latest reply: Apr 11, 2018 12:01 PM by Maria Sandorova RSS

    SQLTables - TABLE_OWNER

    Maria Sandorova
    Maria Sandorova Apr 11, 2018 11:08 AM

      Hi all,

       

      according to help https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/SQLTables.htm in outputs of SQLTables function for OLE DB there should be information about table owner - but there si not such field. I have QS November 2017.

      Any idea where can I find the information?

       

      Thanks,

      Maria

        • Re: SQLTables - TABLE_OWNER
          Petter Skjolden
          Petter Skjolden Apr 11, 2018 11:40 AM (in response to Maria Sandorova )

          This is from the Microsoft SQL Server documentation:

           

          • The following columns have been renamed for ODBC 3.x. The column name changes do not affect backward compatibility because applications bind by column number.

           

          ODBC 2.0 columnODBC 3.x column
          TABLE_QUALIFIERTABLE_CAT
          TABLE_OWNERTABLE_SCHEM

           

          TABLE_OWNER has been renamed to TABLE_SCHEM and you will find that after executing SQLTables.

           

          To be precise you may want to bring in the two extra tables to get more information and current and accurate ownership: msdb.sys.database_principals and msdb.sys.schemas.

           

          LIB CONNECT TO 'MSSQL';


PRINCIPALS:
SQL SELECT 
 name,
    "principal_id",
    "type",
    "type_desc",
    "default_schema_name",
    "create_date",
    "modify_date",
    "owning_principal_id",
    sid,
    "is_fixed_role",
    "authentication_type",
    "authentication_type_desc",
    "default_language_name",
    "default_language_lcid",
    "allow_encrypted_value_modifications"
FROM msdb.sys."database_principals";


SCHEMAS:
SQL SELECT 
    name AS TABLE_SCHEM, // to connect it to the correct field in SQLTABLES later
    "schema_id",
    "principal_id"
FROM msdb.sys.schemas;




SQLTABLES:
SQLTables;