Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This is from the Microsoft SQL Server documentation:
ODBC 2.0 column | ODBC 3.x column |
---|---|
TABLE_QUALIFIER | TABLE_CAT |
TABLE_OWNER | TABLE_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;
This is from the Microsoft SQL Server documentation:
ODBC 2.0 column | ODBC 3.x column |
---|---|
TABLE_QUALIFIER | TABLE_CAT |
TABLE_OWNER | TABLE_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;
Thank you .