find open transacton

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#OpenTranStatus', N'U') IS NOT NULL
    DROP TABLE #OpenTranStatus;
GO
IF OBJECT_ID(N'tempdb..#OpenTranStatusStg', N'U') IS NOT NULL
    DROP TABLE #OpenTranStatusStg;
GO

-- Create the temporary table to accept the results.
CREATE TABLE #OpenTranStatus
    (
    -- dbname NVARCHAR(40),
	dbname varchar(100),
	  ActiveTransaction VARCHAR(25) ,
      Details SQL_VARIANT
    );

	CREATE TABLE #OpenTranStatusStg
    (
    -- dbname NVARCHAR(40),
	  ActiveTransaction VARCHAR(25) ,
      Details SQL_VARIANT
    );

DECLARE @dbid int, @dbname nvarchar(50), @SQLStmt nvarchar(1000);

DECLARE db_cursor CURSOR FOR
select database_id dbid, name dbname from sys.databases where state_desc ='ONLINE'
ORDER BY name;

OPEN db_cursor

FETCH NEXT FROM db_cursor
INTO @dbid, @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #OpenTranStatusStg
SET @SQLStmt = 'DBCC OPENTRAN ('+cast(@dbid as nvarchar(100))+') WITH TABLERESULTS, NO_INFOMSGS'
INSERT  INTO #OpenTranStatusStg
        EXEC ( @SQLStmt)
INSERT INTO #OpenTranStatus SELECT @dbname,* FROM #OpenTranStatusStg

        -- Get the next vendor.
    FETCH NEXT FROM db_cursor
    INTO @dbid, @dbname
END
CLOSE db_cursor;
DEALLOCATE db_cursor;


select * from #OpenTranStatus