ict.ken.be

--only since last restart sql-server
--SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*

select *
from sys.tables
where name not in
(
select o.name
FROM sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
WHERE s.database_id = DB_ID( 'DATABASE_NAME')
--ORDER by s.last_user_update desc
)
order by modify_date desc

select *
from sys.tables t
where name like 'TMP_%'
and create_date < getdate() - 3
order by create_date desc

/* 
CREATE PROCEDURE [DBA_MESSAGENT].[SP_System_Delete_TMP_Tables]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @tableName nvarchar(128)
DECLARE cursorTables CURSOR FOR
select t.name from sys.tables t where t.Name like 'TMP_%' and create_date < getdate() - 3
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN

 DECLARE @sqlSelect NVARCHAR(4000)
 SET @sqlSelect = 'DROP TABLE [' + @tableName + ']'
 EXEC SP_EXECUTESQL @sqlSelect

 FETCH NEXT FROM cursorTables INTO @tableName
END
CLOSE cursorTables
DEALLOCATE cursorTables
 
END
*/

using (var txn = new TransactionScope(TransactionScopeOption.Required,

new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted } ))

{

// Your nolock LINQ to SQL query goes here

}

more: Hanselman on NOLOCK with Linq