SQL Server 2014 – How to Find Ownership for a Given Temp Table

sql serversql server 2014

Is there a good way to find out which user and process owns a given user created temp table in tempdb?

Best Answer

You can use this query, courtesy of Paul White:

SELECT SUSER_NAME(CONVERT(INTEGER, OBJECTPROPERTYEX(t.OBJECT_ID, 'OwnerId')))
FROM tempdb.sys.tables AS t
WHERE t.name LIKE N'#TempTableName%';

Related Solutions

Sql-server – Find out what setting were used for a given stored procedure

This can be determined from sys.sql_modules:

USE tempdb;
GO
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.foo1 AS SELECT 1;
GO
SET ANSI_NULLS OFF;
GO
CREATE PROCEDURE dbo.foo2 AS SELECT 1;
GO

SELECT name = OBJECT_NAME([object_id]), uses_ansi_nulls
  FROM sys.sql_modules
  WHERE OBJECT_NAME([object_id]) IN (N'foo1', N'foo2');

GO
DROP PROCEDURE dbo.foo1, dbo.foo2;

Results:

name   uses_ansi_nulls
----   ---------------
foo1   1
foo2   0

Some other useful columns:

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

Note that some settings (e.g. arithabort, ansi_warnings, ansi_padding) are not stored with the object. Also you will need to recompile the procedures with the correct settings for any that have been identified as "wrong."

Sql-server – copy the table structure of a #temp table to a new physical table

Why not use the same approach you used to create the #temp table?

SELECT * INTO dbo.NewTable FROM #tempTable;

Best Answer

Related Solutions

Sql-server – Find out what setting were used for a given stored procedure

Sql-server – copy the table structure of a #temp table to a new physical table

Related Question