Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
jamajka1
Contributor

SQLTables - TABLE_OWNER

Hi all,

according to help https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/ScriptRegularStateme... 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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: SQLTables - TABLE_OWNER

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;

2 Replies
MVP
MVP

Re: SQLTables - TABLE_OWNER

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;

jamajka1
Contributor

Re: SQLTables - TABLE_OWNER

Thank you .