Hello all,
I'm trying to (partially) reverse engineer how an ERP system is working. It uses M$ SQL Server and it has hundreds of tables. I know most of the tables that store actual data, but I know that there must be tables where the business rules are stored. Is there a way to query a system table for which table just had a record added to it?
There are lots of pages showing how to get the last record added in a table, but this isn't what I'm needing. I'm wanting to add a (test) business rule, and then find what table(s) that got written into.
I believe this query gives me the last time the table structure has changed, but that's not what I want either: SELECT [name],create_date,modify_date FROM sys.tables order by modify_date desc
Thanks, Kevin
This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication.
Thank you.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Is it possible you can turn on a log, make your change, turn it off, and read the log?
On Tue, Jun 6, 2017 at 4:46 PM, Kevin J Cully kjcully@cherokeega.com wrote:
Hello all,
I'm trying to (partially) reverse engineer how an ERP system is working. It uses M$ SQL Server and it has hundreds of tables. I know most of the tables that store actual data, but I know that there must be tables where the business rules are stored. Is there a way to query a system table for which table just had a record added to it?
There are lots of pages showing how to get the last record added in a table, but this isn't what I'm needing. I'm wanting to add a (test) business rule, and then find what table(s) that got written into.
I believe this query gives me the last time the table structure has changed, but that's not what I want either: SELECT [name],create_date,modify_date FROM sys.tables order by modify_date desc
Thanks, Kevin
This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication.
Thank you.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
ODBC has logging facilities, so you could do it for your connection only.
On Tue, Jun 6, 2017 at 4:54 PM, Ted Roche tedroche@gmail.com wrote:
Is it possible you can turn on a log, make your change, turn it off, and read the log?
On Tue, Jun 6, 2017 at 4:46 PM, Kevin J Cully kjcully@cherokeega.com wrote:
Hello all,
I'm trying to (partially) reverse engineer how an ERP system is working. It uses M$ SQL Server and it has hundreds of tables. I know most of the tables that store actual data, but I know that there must be tables where the business rules are stored. Is there a way to query a system table for which table just had a record added to it?
There are lots of pages showing how to get the last record added in a table, but this isn't what I'm needing. I'm wanting to add a (test) business rule, and then find what table(s) that got written into.
I believe this query gives me the last time the table structure has changed, but that's not what I want either: SELECT [name],create_date,modify_date FROM sys.tables order by modify_date desc
Thanks, Kevin
This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication.
Thank you.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I'm adding the business rule through their ERP system which I don't think uses the ODBC connection. But your suggestion about looking through the logs is a good one! I'll take a gander at the system logs when I add records into the test system. Thanks!
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Ted Roche Sent: Tuesday, June 06, 2017 4:55 PM To: profox@leafe.com Subject: Re: [NF] Find table where last record was added
ODBC has logging facilities, so you could do it for your connection only.
On Tue, Jun 6, 2017 at 4:54 PM, Ted Roche tedroche@gmail.com wrote:
Is it possible you can turn on a log, make your change, turn it off, and read the log?
On Tue, Jun 6, 2017 at 4:46 PM, Kevin J Cully kjcully@cherokeega.com wrote:
Hello all,
I'm trying to (partially) reverse engineer how an ERP system is working. It uses M$ SQL Server and it has hundreds of tables. I know most of the tables that store actual data, but I know that there must be tables where the business rules are stored. Is there a way to query a system table for which table just had a record added to it?
There are lots of pages showing how to get the last record added in a table, but this isn't what I'm needing. I'm wanting to add a (test) business rule, and then find what table(s) that got written into.
I believe this query gives me the last time the table structure has changed, but that's not what I want either: SELECT [name],create_date,modify_date FROM sys.tables order by modify_date desc
Thanks, Kevin
This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication.
Thank you.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
In Dev:
1. Run a query that gives you the row counts of all the tables in your schema. Save the results. 2. Enter your new records.
3. Re-run the your query from 1 and compare record counts to see which tsble(s) grew.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Kevin, Does this help?
https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-datab...
You may need a little work to get what you want but it seems like all the info you want is in the log.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@mail.leafe.com] On Behalf Of Kevin J Cully Sent: 06 June 2017 21:46 To: 'ProFox Email List' profox@leafe.com Subject: [NF] Find table where last record was added
Hello all,
I'm trying to (partially) reverse engineer how an ERP system is working. It uses M$ SQL Server and it has hundreds of tables. I know most of the tables that store actual data, but I know that there must be tables where the business rules are stored. Is there a way to query a system table for which table just had a record added to it?
There are lots of pages showing how to get the last record added in a table, but this isn't what I'm needing. I'm wanting to add a (test) business rule, and then find what table(s) that got written into.
I believe this query gives me the last time the table structure has changed, but that's not what I want either: SELECT [name],create_date,modify_date FROM sys.tables order by modify_date desc
Thanks, Kevin
This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication.
Thank you.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Depending on your rights you can use Profile.exe and run it against the db in question. There is an amazing amount of information it could present to you and watching a quick Youtube on it will save you a lot of initial extra data you don't want to see.
I would avoid logs as a first step in the what is happening question and go for the profiler and a new trace.
I use this to identify how statements are being passed n from our ERP which is java and it uses command line data inputs and no "connection" to the db.
Profiler does give a shit ton of data that is happening because SQL Server is not an easy peasy toy.
Saving to a table generates insert statements that you will see happening just as a heads up. Try to use file instead. Go to the events selection and uncheck what you do not want to see. This will help reduce the noise that the file will collect.
Welcome to the bomb squad. As you see all of this data about what is really happening on the system may realize how complicated the beast really is, and one wrong move can literally blow things up.
On Wed, Jun 7, 2017 at 5:55 AM, Dave Crozier DaveC@flexipol.co.uk wrote:
Kevin, Does this help?
https://www.mssqltips.com/sqlservertip/3076/how-to-read- the-sql-server-database-transaction-log/
You may need a little work to get what you want but it seems like all the info you want is in the log.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@mail.leafe.com] On Behalf Of Kevin J Cully Sent: 06 June 2017 21:46 To: 'ProFox Email List' profox@leafe.com Subject: [NF] Find table where last record was added
Hello all,
I'm trying to (partially) reverse engineer how an ERP system is working. It uses M$ SQL Server and it has hundreds of tables. I know most of the tables that store actual data, but I know that there must be tables where the business rules are stored. Is there a way to query a system table for which table just had a record added to it?
There are lots of pages showing how to get the last record added in a table, but this isn't what I'm needing. I'm wanting to add a (test) business rule, and then find what table(s) that got written into.
I believe this query gives me the last time the table structure has changed, but that's not what I want either: SELECT [name],create_date,modify_date FROM sys.tables order by modify_date desc
Thanks, Kevin
This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication.
Thank you.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]