The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Looking for a SecTableDefs Trick
Posted: 08 Aug 06 12:57 PM
|
fiogf49gjkf0d All,
Does anybody have a down and dirty way to check objects in the database against SecTableDefs? I am analyzing
a modified DB schema that was implemented outside of SLX DBManager. (i.e.: Fields have been added to base A/C/O and other tables directly from SQL)  I know I can query the syscolumns and get a handful of items, but the nomenclature is not 100% standard for the custom fields.
thx,
Carla |
|
|
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 1:53 PM
|
fiogf49gjkf0d Tables that are in ReSyncTableDefs but not in the database:
SELECT
R.[TableName]
FROM
[sysdba].[ReSyncTableDefs] R
LEFT JOIN [Information_Schema].[Tables] T
ON T.[Table_Name] = R.[TableName] AND
T.[Table_Type] = 'Base Table' AND
T.[Table_Schema] = 'sysdba'
WHERE
T.[Table_Name] IS NULL
ORDER BY
R.[TableName]
Tables in the database (and owned by sysdba) that are not in ReSyncTableDefs:
SELECT
T.[Table_Name]
FROM
[Information_Schema].[Tables] T
LEFT JOIN [sysdba].[ReSyncTableDefs] R
ON T.[Table_Name] = R.[TableName]
WHERE
T.[Table_Type] = 'Base Table' AND
T.[Table_Schema] = 'sysdba' AND
R.[TableName] IS NULL
ORDER BY
T.[Table_Name]
Timmus |
|
|
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 2:03 PM
|
fiogf49gjkf0d Timmus, my man!
Thanks for the speedy reply. Unfortunately this did not capture what I am looking for, but it did bring up a question; ResyncTableDefs vs. SecTableDefs - What is the deal-i-o between these two entities?
Carla |
|
|
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 2:06 PM
|
fiogf49gjkf0d You are welcome.  So what are you looking for?
SecTableDefs is column level metadata. ReSyncTableDefs is table level metadata. Does that answer your question?
Timmus |
|
|
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 2:11 PM
|
fiogf49gjkf0d Here are some more queries that report on issues in SecTableDefs:
Columns in SecTableDefs that are not in the database:
SELECT
S.[TableName], S.[FieldName]
FROM
[sysdba].[SecTableDefs] S
LEFT JOIN
(
SELECT
C.[Table_Name], C.[Column_Name]
FROM
[Information_Schema].[Columns] C
INNER JOIN [Information_Schema].[Tables] T
ON C.[Table_Name] = T.[Table_Name] AND
T.[Table_Type] = 'Base Table' AND
T.[Table_Schema] = 'sysdba'
) C
ON S.[TableName] = C.[Table_Name] AND
S.[FieldName] = C.[Column_Name]
WHERE
C.[Column_Name] IS NULL
ORDER BY
S.[TableName], S.[FieldName]
Columns in the database but not in SecTableDefs (for sysdba tables of course). Note that this also assumes that the tables are in ReSyncTableDefs.
SELECT
C.[Table_Name], C.[Column_Name], C.[Data_Type], C.[Ordinal_Position]
FROM
[Information_Schema].[Columns] C
INNER JOIN [Information_Schema].[Tables] T
ON C.[Table_Name] = T.[Table_Name] AND
T.[Table_Type] = 'Base Table' AND
T.[Table_Schema] = 'sysdba'
INNER JOIN [sysdba].[ReSyncTableDefs] R
ON R.[TableName] = T.[Table_Name]
LEFT JOIN [sysdba].[SecTableDefs] S
ON S.[TableName] = C.[Table_Name] AND
S.[FieldName] = C.[Column_Name]
WHERE
S.[FieldName] IS NULL
ORDER BY
C.[Table_Name], C.[Column_Name]
Columns in the database but not in SecTableDefs (for sysdba tables of course). Note that this DOES NOT assume that the tables are in ReSyncTableDefs. In the case where you are just reporting the issues and not fixing them, this makes more sense. If you are fixing the metadata, I suggest starting with ReSyncTableDefs. Then once that data is correct you will want to include the inner join to ReSyncTableDefs. You dont want columns listed in SecTableDefs that belong to tables that are not in ReSyncTableDefs.
SELECT
C.[Table_Name], C.[Column_Name], C.[Data_Type], C.[Ordinal_Position]
FROM
[Information_Schema].[Columns] C
INNER JOIN [Information_Schema].[Tables] T
ON C.[Table_Name] = T.[Table_Name] AND
T.[Table_Type] = 'Base Table' AND
T.[Table_Schema] = 'sysdba'
LEFT JOIN [sysdba].[SecTableDefs] S
ON S.[TableName] = C.[Table_Name] AND
S.[FieldName] = C.[Column_Name]
WHERE
S.[FieldName] IS NULL
ORDER BY
C.[Table_Name], C.[Column_Name]
Timmus (sorry for the multiple edits - trying to figure out formatting ) |
|
|
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 3:20 PM
|
fiogf49gjkf0d
SecTableDefs is column level metadata. ReSyncTableDefs is table level metadata. Does that answer your question?
Timmus
That's what I was asking! All this time... I have never used the ResyncTableDefs table.
I am looking into the other queries you kindly supplied. Hopefully I will end up with a full listing of 'unauthorized' customizations and move on.
c |
|
|
| |
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 6:21 PM
|
fiogf49gjkf0d Originally posted by Carla Tillman
You are an incredible time saver |
|
This is the result of my aversion to work of any kind :p
I am happy it helped.
Timmus |
|
|
|
Re: Looking for a SecTableDefs Trick
Posted: 08 Aug 06 6:30 PM
|
fiogf49gjkf0d Originally posted by Timmus Agersea
sorry for the multiple edits - trying to figure out formatting) |
|
Hehe. Sorry about that. I do have some changes coming to the forums (hopefully soon) that will make posting code a whole lot easier.
-Ryan |
|
|
|