Databases
StackOverflow2013
Postsdbo
POHow to get a database object based on an OBJECT_ID?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get a database object based on an OBJECT_ID?
    primarykey
    data
    text
    <p>In SQL Server 2008, is there a way to access an object based on the <code>OBJECT_ID</code>?</p> <p>[Edit] The example described below was solved using Andrew's suggestion in the comment, but I'm still curious about the general case. Can an object itself be retrieved using <code>OBJECT_ID</code>, or can it only be accessed indirectly by using the object name via <code>sp_executesql</code>?</p> <p>My specific case is a stored procedure that uses several temporary tables. At the end of the procedure I want to dump the data from the temporary tables into actual tables for analysis (only if a debug switch is enabled).</p> <p>The code for dumping the data is similar to this:</p> <pre><code>IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL BEGIN IF OBJECT_ID('Debug_MyTempTable', 'U') IS NOT NULL DROP TABLE Debug_MyTempTable SELECT * INTO Debug_MyTempTable FROM #MyTempTable END </code></pre> <p>This code block is repeated for each temporary table, so I would prefer to put it in a procedure and call it with a table name:</p> <pre><code>EXEC [dbo].[CreateDebugTable] @tableName = 'MyTempTable' </code></pre> <p>I imagine the procedure would look something like:</p> <pre><code>CREATE PROCEDURE [dbo].[CreateDebugTable] @tableName VARCHAR(50) AS BEGIN IF OBJECT_ID('tempdb..#' + @tableName, 'U') IS NOT NULL BEGIN IF OBJECT_ID('dbo.Debug_' + @tableName, 'U') IS NOT NULL DROP TABLE &lt;Debug_TempTable&gt; SELECT * INTO &lt;Debug_TempTable&gt; FROM &lt;#TempTable&gt; END END </code></pre> <p>The procedure depends on being able to translate the <code>OBJECT_ID</code> of <code>DebugTempTable</code> and <code>#TempTable</code> into the actual tables (shown with &lt;> in the code above). Is this possible?</p> <p>[Edit] This is the altered procedure using <code>sp_executesql</code> instead of explicit tables.</p> <pre><code>CREATE PROCEDURE [dbo].[CreateDebugTable] @tableName VARCHAR(50) AS BEGIN DECLARE @tmpTable VARCHAR(50) = '#' + @tableName DECLARE @dboTable VARCHAR(50) = 'Debug_' + @tableName DECLARE @sql NVARCHAR(100) IF OBJECT_ID('tempdb..' + @tmpTable, 'U') IS NOT NULL BEGIN IF OBJECT_ID('dbo.' + @dboTable, 'U') IS NOT NULL BEGIN SET @sql = 'DROP TABLE ' + @dboTable EXECUTE sp_executesql @sql END SET @sql = 'SELECT * INTO ' + @dboTable + ' FROM ' + @tmpTable EXECUTE sp_executesql @sql END END </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWileCau
    1. USWileCau
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCouldn't you just sp_executesql the statement instead and not worry about converting it back? You seem to have the names as strings, so generate a stmt and execute it- or am I missing part of the problem?
      singulars
      1. POHow to get a database object based on an OBJECT_ID?
      1. USAndrew
    2. CO@Andrew, yes you're right I could do that. I missed the obvious :) Thanks
      singulars
      1. POHow to get a database object based on an OBJECT_ID?
      1. USWileCau
    3. CONp, question probs has limited value to keep so I would recommend you just delete it.
      singulars
      1. POHow to get a database object based on an OBJECT_ID?
      1. USAndrew
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload