SQL Server 2012 - where is the table in dbo? [closed]

Question

I am trying to insert something into the dbo.Documents table. After the query is run, I can't find the Documents under the TSQL2012 database. I created TSQL2012 for exercise purpose.

I couldn't find the table Documents created in the TSQL2012 database. Please give me some advises on where this table is created. Thanks!

Here is the query:

 INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Additivity of Measures', 
 N'docx',
 N'Additivity of measures is not exactly a data warehouse design problem. 
   However, you have to realize which aggregate functions you will use 
   in reports for which measure, and which aggregate functions 
   you will use when aggregating over which dimension.',
 bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\admin121\Downloads\9780735666054_Files\Chapter 06\AdditivityOfMeasures.docx', 
                SINGLE_BLOB) AS doc;

Answer 1

Oftentimes this problem stems from one of two scenarios:

Either you created the table in the wrong database, or you never created the table at all (user error or just forgetfulness. It happens to us all).

What you need to do is look through each of your databases for your table. You can do this through the sys.tables system catalog view in your instance's databases. Usually in SSMS people forget to change the database context from master to their desired database, causing DDL to execute in master. Regardless, master is a good first place to look.

Answer 2

How to find a list of available tables using the sys.tables that have a name with documents in the name:

SELECT *
FROM sys.Tables
WHERE name LIKE '%documents%'

There is a limitation of this query that it will only search in one database and you have to keep on changing the target database manually until you find the missing table. This script, which looks into all the databases on the server, will provide the database name, schema name and tables contained on the target server.

CREATE PROCEDURE usp_FindYourMissingTable
    @TableName VARCHAR(256)
AS

DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
    SELECT name
    FROM sys.databases
    CREATE TABLE #TmpTable (DBName VARCHAR(256),
    SchemaName VARCHAR(256),
    TableName VARCHAR(256))
    OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @varSQL = 'USE ' + @DBName + ';
    INSERT INTO #TmpTable
    SELECT '''+ @DBName + ''' AS DBName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName
    FROM sys.tables
    WHERE name LIKE ''%' + @TableName + '%'''
    EXEC (@varSQL)
    FETCH NEXT
    FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT * FROM #TmpTable
DROP TABLE #TmpTable
GO

Just execute the stored procedure using:

EXEC usp_FindYourMissingTable 'documents'
GO 

Answer 3

If you're able to insert into documents right now, it's in whatever database is your current context.

You can find tables in the future with the undocumented sp_msforeachdb command:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select ''[?]'' as DB, name from sys.tables where name=''documents'''

Answer 4

insert into .. select will copy data to an existing table. Is the table dbo.Documents created ? Also what does select name from sysobjects where name = 'Documents' and type = 'U' gives ? The table name should be there else it is created in wrong database or it is not created at all.

You will find the table under the database where you did the select * from … Or possibly, you might have created it under different schema

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables where name = 'documents'

This will give you schema and table name.