How can I see which tables are changed in SQL Server?

Question

I have a database which I didn't make and now I have to work on that database. I have to insert some information, but some information must be saved in not one table but several tables. I

can use the program which have made the database and insert information with that. While I am doing that, I want to see that which tables are updated. I heard that SQL Server Management Studio has a tool or something which make us see changes.

Do you know something like that? If you don't, how can I see changes on the database's tables? If you don't understand my question, please ask what I mean. Thanks

Edit : Yes absolutely Sql Profiler is what I want but I am using SQL Server 2008 R2 Express and in Express edition, Sql Profiler tool does not exist in Tools menu option. Now I am looking for how to add it.

Edit 2 : Thank you all especially @SchmitzIT for his pictured answer. I upgraded my SQL Server Management Studio from 2008 R2 express edition to 2012 Web Developer Edition. SQL Profiller Trace definitely works.

Answer 1

Although you describe in your question what you want, you don't explain why you want it. This would be helpful to properly answer your question.

ExpressProfiler is a free profiler that might meet your needs.

If you're looking to track DDL changes to your database, rather than all queries made against it, you might find SQL Lighthouse useful, once it is released in Beta shortly.

Disclosure: I work for Red Gate.

Answer 2

I agree with @Lmu92. SQL Server Profiler is what you want.

From SQL Server Management Studio, click on the "Tools" menu option, and then select to use "SQL SErver Profiler" to launch the tool. The profier will allow you to see statements executed against the database in real time, along with statistics on these statements (time spent handling the request, as well as stats on the impact of a statement on the server itself).

The statistics can be a real help when you're troubleshooting performance, as it can help you identify long running queries, or queries that have a significant impact on your disk system.

On a busy database, you might end up seeing a lot of information zip by, so the key to figuring out what's happening behind the scenes is to ensure that you implement proper filtering on the events.

To do so, after you connect Profiler to your server, in the "Trace properties" screen, click the "Events Selection" tab:

You probably are good to uncheck the boxes in front of the "Audit" columns, as they are not relevant for your specific issue. However, the important bit on this screen is the "Column filters" button:

This is where you will be able to implement filters that only show you the data you want to see. You can, for instance, add a filter to the "ApplicationName", to ensure you only see events generated by an application with the name you specify. Simply click on the "+" sign next to "Like", and you will be able to fill in an application name in the textbox.

You can choose to add additional filters if you want (like "NTUsername" to filter by AD username, or "LoginName" for an SQL Server user.

Once you are satisfied with the results, click "OK", and you will hopefully start seeing some results. Then you can simply use the app to perform the task you want while the profiler trace runs, and stop it once you are done.

You can then scroll through the collected data to see what exactly it has been doing to your database. Results can also be stored as a table for easy querying.

Hope this helps.

Answer 3

As far as I know SQL Profiler tool is not included in the Express edition of SQL Server (only Developer edition contains it). Maybe you can purchase it separately. If you want you can try this https://sites.google.com/site/sqlprofiler/ free tool for profiling. Maybe it has limitations, but it you can check it.

There are also some profiling tools for integration with Visual studio, and also the ReSharper has InteliTrace (if you are using the tool at all).

Cheers.

Answer 4

Do you know something like that? If you don't, how can I see changes on the database's tables? If you don't understand my question, please ask what I mean. Thanks

• Red gate makes a lot of great tools. You can always make a database comparison. That will require that you make a backup, then doing whatever you want to do, and the compare the two databases, there is a free trial, works perfect to compare which tables has changed (deleted/inserted and modified)

http://www.red-gate.com/products/sql-development/sql-data-compare/

what you might maybe be looking for is "SQL Server Profiler". It's a SQL monitor where you can see all the SQL queries running real time.

http://technet.microsoft.com/en-us/library/aa173918(v=sql.80).aspx

Hope that helps you.