I am looking for a way for SQL Server Management studio to show me the last SQL query that was run. For example, when I make modifications to a table in the designer mode, I will eventually want to make the same changes to the production DB without recreating the entire table. I’m looking for functionality similar to PHPMyAdmin for MySQL which tells you the last query ran and the results of the query at the top of the page after the modifications are complete.
If you want to know what is the query corresponding to the change you’ve done to a table (or other object) in designer mode, use Generate Change Script (Table Designer → Generate Change Script… or the very first icon, with a diskette and a script, in Table Designer toolbar) command.
Note that actually you don’t need to commit the changes, which makes it a good medium to learn things. For example, if you’ve forgotten how to create a primary key which spans on multiple columns, one way is to Google it; another one is to create such key and see the change script.
If you rather want to track all the queries, not just the ones made by the designer, use the profiler (Tools → Sql Server Profiler).
1
If you have more than one developer making changes to your schema, then you might need a tool like Redgate SQL Compare or similar create change scripts between environments.
Although more of an admin tool than a developer tool, dbcc inputbuffer({spid}) will get the current / last executed sql statement on a per connection level. It won’t however return anything useful on the current connection (dbcc inputbuffer(@@SPID) returns dbcc inputbuffer(@@SPID) :P))
At a server / audit level, SQL Server Audit can assist with tracking changes to your schema.
SQL Audit could definitely catch the changes but the audit events can be a little cryptic. LOGbinder SQL translates them into readable English. I think the Audit Action Group you’d want to enable is SCHEMA_OBJECT_CHANGE_GROUP
WHy are you not making the changes through scripts and committing them to source control like any other code? Then you know what is supposed to be pushed to prod.