When running a script with several commands, how to find out where we're at?

I agree with Aaron that RAISERROR...WITH NOWAIT can be very useful and is probably the way to go if you have full control over the script that is being generated.

However, if a long script is currently executing and you don't have the ability to change the script in order to add RAISERROR calls, there are also less direct ways to get this information.

Test script

Here is a test script you can run to help demonstrate the two approaches below:

SELECT 1
WAITFOR DELAY '00:00:15'
SELECT 2
WAITFOR DELAY '00:00:15'
SELECT 3

sp_whosiasctive

While running this script, you can use sp_whoisactive to view the current server activity. You can often view the query plan for the specific statement that is currently executing. In my case, I see the following because the WAITFOR statement is most likely to be running at any given moment in time:

Using sys.dm_exec_requests.statement_start_offset

Alternatively, Conor Cunningham also has a post on extracting the statement from sys.dm_exec_query_stats AND sys.dm_exec_sql_text. I don't believe this has been incorporated into sp_whoisactive yet, but you can use a query like the following to see both the current executing statement and the overall batch.

SELECT er.session_Id AS spid
    --Use the full batch text and the start/end offset of the currect statement to figure 
    --out the SQL that is currently executing. This logic is based on the blog post above
    --but has been updated in light of strange cases in SQL Server that caused the original
    --blog post logic to crash with out of bounds errors on the SUBSTRING operation.
    , SUBSTRING (qt.text 
                , (CASE WHEN er.statement_start_offset > DATALENGTH(qt.text) 
                    THEN 0 ELSE er.statement_start_offset/2 END)+1
                , (CASE WHEN er.statement_end_offset <= 0 THEN DATALENGTH(qt.text)
                    ELSE er.statement_end_offset 
                    END - CASE WHEN er.statement_start_offset > DATALENGTH(qt.text) 
                        THEN 0 ELSE er.statement_start_offset/2 END)
                    + 1
                ) AS query
    , qt.text AS parent_query
FROM sys.dm_exec_requests er
JOIN sys.dm_exec_sessions s
    ON s.session_id = er.session_id
    AND s.session_id <> @@SPID      -- Ignore this current statement.
    AND s.is_user_process = 1       -- Ignore system spids.
    AND s.program_name NOT LIKE '%SQL Server Profiler%' -- Ignore profiler traces
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
ORDER BY spid

Here is one way to generate a script that outputs data to the screen when one command is complete, and announces the next one as well. The important thing is to use RAISERROR with NOWAIT so that you aren't depending on the buffer output manager in SSMS deciding when you should see PRINT output in the messages pane.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'  RAISERROR(''Next is ' 
  + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + ''',0,1) WITH NOWAIT;
  --DELETE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + ';
  RAISERROR(''Finished ' 
  + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + ''',0,1) WITH NOWAIT;'
FROM saproduct.sys.schemas AS s
INNER JOIN saproduct.sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE t.name LIKE N'tbl%';

PRINT @sql;
--EXEC saproduct.sys.sp_executesql @sql;

This will have output like this (remove the -- when you want to really run it):

  RAISERROR('Next is [dbo].[Table1]',0,1) WITH NOWAIT;
  --DELETE [dbo].[Table1];
  RAISERROR('Finished [dbo].[Table1]',0,1) WITH NOWAIT;
  RAISERROR('Next is [dbo].[Table2]',0,1) WITH NOWAIT;
  --DELETE [dbo].[Table2];
  RAISERROR('Finished [dbo].[Table2]',0,1) WITH NOWAIT;
  ...

(Why I use the proprietary catalog views instead of the incomplete INFORMATION_SCHEMA views.)