I have used Microsoft SQL Server a lot and done some work on data migrations. That means moveing large or huge amount of data between databases and between different schemas. For these kinds of tasks, I normaly prefer writing scripts to do the job and executing the scripts from batch scripts. While executing migration scripts from command line keeping track of what is running now and some information about the status is good to know. Otherwise debugging and improving the scripts are very hard.
SELECT r.start_time [Start Time],session_ID [SPID], DB_NAME(database_id) [Database], SUBSTRING(t.text,(r.statement_start_offset/2)+1, CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 ELSE (r.statement_end_offset-r.statement_start_offset)/2+1 END) [Executing SQL], Status,command,wait_type,wait_time,wait_resource, last_wait_type FROM sys.dm_exec_requests r OUTER APPLY sys.dm_exec_sql_text(sql_handle) t WHERE session_id != @@SPID -- don't show this query AND session_id > 50 -- don't show system queries ORDER BY r.start_time
The script above is what I am using to see what is executing on a M-SQL server now. The scripts use the system tables to get the command executing and also using the session id field to not list itself. System queries are also nice to keep out to not have all the noise that most of the time is not needed to see how the execution is going. If there are more users executing scripts on the server and filtering to a spesific login name may be useful. Then the dm_exec_sessions can be joined into the query above.
select session_id, login_name from sys.dm_exec_sessions