Is it possible to track the changes in the users tables in a database withouth the track changing enabled? If I turn it on will be able to see tha latest changes in the user database tables? And how do I do that?

Question

I am new to it and I am required to know the change made in a database DB1, tables lately by some software.

mssql 2008, trackchange turned off, will this system at this state running on windows server 2003 OS will also show the changes in the database tables which I recently mede? i ran the query you made above, the last one. Which only shows some changes in master database. I know the database name on which the changes occour. I want to scan the database and see which table and column are effected by some changes (I know what I have entered in the fields provided by teh software, I do not know the mapping of those tables to the database table so need to figure them out) in the running software.  Do i require to change the state of the TRACK CHANGE  of the database to know it?  waiting for your response.

By now I have enabled tarck change, how do I get the last updated users tables and its columns.

Answer 1

Hi toughdeep,

Base on your description, you have enabled Change Tracking for a Database. If you want to get the last updated user table and its columns, you have to enable Change Tracking for a Table. You can enable change tracking for a table by using ALTER TABLE:

ALTER TABLE <schema_name>.<table_name>
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Also you can enable tracking for a table in SQL Server Management Studio by using the Table Properties (Change Tracking Page) dialog box. For more details, please refer to this article: Configuring and Managing Change Tracking. Please note that Change Tracking requires a primary key on a table.

After that, you can use the following Change Tracking Functions to obtain the changes that are made in a database and information about the changes:
• CHANGETABLE (CHANGES …) function
The function returns a results set that contains the primary keys of rows that have changed together with other change information such as operation, columns updated and version for the row.
• CHANGE_TRACKING_CURRENT_VERSION() function
It is used to obtain the current version that will be used the next time when querying changes.
• CHANGE_TRACKING_MIN_VALID_VERSION() function
It is used to obtain the minimum valid version that a client can have and still obtain valid results from CHANGETABLE().
For more details, please refer to this article: Obtaining Changes by Using the Change Tracking Functions.

You can take the following sample code as your reference, please note the id column is the primary key of table e

declare @last_synchronization_version bigint
set @last_synchronization_version = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘<schema_name>.<table_name>’))
select CT.id, 
       e.*,
       CT.SYS_CHANGE_OPERATION,         
       CT.SYS_CHANGE_COLUMNS,
       CT.SYS_CHANGE_CONTEXT
FROM   <schema_name>.<table_name> as e
right outer join Changetable(changes <schema_name>.<table_name>,@last_synchronization_version) as CT
on e.id = CT.id

The columns CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT are main information, for more information about them, please refer to this article: CHANGETABLE (Transact-SQL).

Best Regards,
Ray Chen