Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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).

share|improve this answer
1  
In 2008 there is also a checkbox to Automatically generage change script on every save. –  JeffO Aug 21 '12 at 19:00

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.