This script is useful if want to check when transaction log backups were last restored to a specific database.
With minor amendments, you can also check other restore types and check for all databases, not just a specific one.
Send T-SQL query results in a HTML table
Send T-SQL query results in a HTML table
If you need to email out t-sql query results in a HTML table, whether on an ad-hoc basis or regularly via a job, it can be useful to present the results in a HTML formatted table. This makes the output much easier to read for the recipients, especially if they are not used to using management studio and seeing unformatted data. [Read more…]
List all views
This script is handy when you want to list all views on an instance or even find a specific view if you know the name but don’t know the database.
[sql]
— Written by John McCormack
— This script uses the unspported stored procedure sp_msforeachdb so there are more reliable ways of going about this task.
— It uses a table variable and inserts each row returned into the table variable
— The ? is the database – All databases will be
DECLARE @views table (DBName sysname, ViewName nvarchar(100))
INSERT INTO @views
EXEC sp_msforeachdb
‘USE ?
SELECT ”?”,name FROM sys.views
— where [name] = ”vw_CurrentDaySales”’
— Uncomment the where clause if you wanted to find a specific view
SELECT * FROM @views
[/sql]
Check percentage complete of transaction
SELECT percent_complete,* FROM sys.dm_exec_requests WHERE session_id = 63 -- Remember to change session_id = to the relevant spid /* This simple script shows what percentage of a transaction has completed This works for the following types of transaction: ALTER INDEX REORGANIZE AUTO_SHRINK option with ALTER DATABASE BACKUP DATABASE DBCC CHECKDB DBCC CHECKFILEGROUP DBCC CHECKTABLE DBCC INDEXDEFRAG DBCC SHRINKDATABASE DBCC SHRINKFILE RECOVERY RESTORE DATABASE, ROLLBACK TDE ENCRYPTION Change the 63 in the where clause to match the spid for your transaction. Hint exec sp_who2 and look at command. This is the spid you want. */