|
I am working on understanding the SQLA 16/17 system tables and I'm a bit stuck on where to find index FOR OLAP WORKLOAD. I was able to find a reference to SYSPHYSIDX max_key_distance but am unable to figure this out. Here is what I have so far if anyone is interested SELECT ForeignKeys.foreign_table_id AS TableId,
SQL.SmartQuote( ForeignTables.table_name ) AS FTableName,
SQL.SmartQuote( UPPER( ForeignOwners.user_name ) ) AS FTableOwner,
SQL.SmartQuote( PrimaryTables.table_name ) AS PTableName,
SQL.SmartQuote( UPPER( PrimaryOwners.user_name ) ) AS PTableOwner,
SQL.FKIndexColumns(
ForeignKeys.foreign_table_id,
ForeignKeys.foreign_index_id,
1 ) AS FIndexList,
SQL.FKIndexColumns(
ForeignKeys.primary_table_id,
ForeignKeys.primary_index_id,
0 ) AS PIndexList,
IF ForeignTables.clustered_index_id =
ForeignKeys.foreign_index_id THEN 'CLUSTERED'
ELSE NULL
ENDIF AS ClusteredIndex,
CASE ForeignKeys.check_on_commit
WHEN 'Y' THEN 'CHECK ON COMMIT'
ELSE NULL
END AS CheckOnCommit,
CASE ForeignKeys.nulls
WHEN 'N' THEN 'NOT NULL'
ELSE NULL
END AS AllowNull,
CASE ForeignKeys.match_type
WHEN 1 THEN 'MATCH SIMPLE'
WHEN 2 THEN 'MATCH FULL'
WHEN 1 THEN 'MATCH UNIQUE SIMPLE'
WHEN 1 THEN 'MATCH UNIQUE FULL'
ELSE NULL
END AS MatchType,
CASE FKDeleteAction.referential_action
WHEN 'C' THEN 'CASCADE'
WHEN 'D' THEN 'DELETE'
WHEN 'N' THEN 'SET NULL'
WHEN 'R' THEN 'RESTRICT'
ELSE 'RESTRICT'
END AS ONDeleteAction,
CASE FKUpdateAction.referential_action
WHEN 'C' THEN 'CASCADE'
WHEN 'D' THEN 'DELETE'
WHEN 'N' THEN 'SET NULL'
WHEN 'R' THEN 'RESTRICT'
ELSE 'RESTRICT'
END AS ONUpdateAction,
FTConstraint.constraint_name AS ConstraintName,
SQL.ForeignKey( FTableOwner,
FTableName,
AllowNull,
ConstraintName,
FIndexList,
PTableOwner,
PTableName,
PIndexList,
MatchType,
CheckOnCommit,
ClusteredIndex,
ONDeleteAction,
ONUpdateAction) AS SQL
FROM SYS.SysFKey AS ForeignKeys
JOIN SYS.SysIdx AS FKIndex ON ( ForeignKeys.foreign_table_id = FKIndex.table_id AND ForeignKeys.foreign_index_id = FKIndex.index_id )
LEFT OUTER
JOIN SYS.SysTrigger AS FKDeleteAction ON ( FKIndex.table_id = FKDeleteAction.foreign_table_id AND FKIndex.index_id = FKDeleteAction.foreign_key_id AND FKDeleteAction.event = 'D')
LEFT OUTER
JOIN SYS.SysTrigger AS FKUpdateAction ON ( FKIndex.table_id = FKUpdateAction.foreign_table_id AND FKIndex.index_id = FKUpdateAction.foreign_key_id AND FKUpdateAction.event = 'C')
JOIN SYS.SysTab AS ForeignTables ON ( ForeignKeys.foreign_table_id = ForeignTables.table_Id )
JOIN SYS.SysConstraint AS FTConstraint ON ( ForeignTables.object_id = FTConstraint.table_object_id AND FTConstraint.constraint_type = 'F' AND FKIndex.object_id = FTConstraint.ref_object_id )
JOIN SYS.SysUser AS ForeignOwners ON ( ForeignTables.creator = ForeignOwners.user_id )
JOIN SYS.SysTab AS PrimaryTables ON ( ForeignKeys.primary_table_id = PrimaryTables.table_Id )
JOIN SYS.SysUser AS PrimaryOwners ON ( PrimaryTables.creator = PrimaryOwners.user_id )
|
|
PowerDesigner can nicely reverse engineer the entire DB from an ODBC connection. |
|
Dbunuload -n will generate all of the DB schema including so is the foreign keys. You could use that and then pull out the text from the sql file? Yes this is a handy utility. I'm attempting to create procedures and views which will provide the same output as DBUnload - n but without items such as comment to preserve format, etc. My goal is to be able to run this as an event in remote databases to validate the schema after an event such as a passthrough. I intend to use the SysSource.Source whenever possible because I have noticed the SQLA interpretation changes depending on version. In the end I will hash the results and compare this against a hash stored in a master schema table and if there is a mismatch send notification to the consolidated. Rather large project but with COVID-19 I need something to do.
(29 Oct '20, 09:09)
J Diaz
|
|
Have you had a look at the sa_get_table_definition system procedure? That might also do the trick for you. (Note: I have not used that myself.) Aside: This seems to be a system function, not a procedure. :) FWIW, this seems to do a job comparable to the DBUNLOAD utility, i.e. the FK definitions are not part of the CREATE TABLE statement but are added with further ALTER TABLE statements for each FK. Wow didn't know it existed thanks!
(29 Oct '20, 11:19)
J Diaz
|
I don't know - but thanks for the pointer to the FOR OLAP WORKLOAD clause, which I seem to have overlooked so far...
I don't know either. Just wanted to point out that the case statement for MatchType contains duplicate when values.
CASE ForeignKeys.match_type WHEN 1 THEN 'MATCH SIMPLE' WHEN 2 THEN 'MATCH FULL' WHEN 1 THEN 'MATCH UNIQUE SIMPLE' WHEN 1 THEN 'MATCH UNIQUE FULL' ELSE NULL ENDShould be
CASE ForeignKeys.match_type WHEN 1 THEN 'MATCH SIMPLE' WHEN 2 THEN 'MATCH FULL' WHEN 129 THEN 'MATCH UNIQUE SIMPLE' WHEN 130 THEN 'MATCH UNIQUE FULL' ELSE NULL ENDThanks very much