Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot, Kin, Max Vernon Jan 1 at 20:51

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot, Kin, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

    
insert into .. select will copy data to an existing table. Is the table dbo.Documents created ? Also what does select name from sys objects 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. –  Kin Dec 31 '13 at 3:52
    
Hello Kin, Thanks for the reply. I create the table using TSQL2012 database (I created it for exercise purpose), Then I create the table Documents. I run the query suggested by you, it return Documents under the name column. Thanks! –  user2061466 Dec 31 '13 at 4:37
    
Then just do a select * from Dcouments to see if the data is there or not. –  Kin Dec 31 '13 at 4:39
    
Hello Kin, I used select * from dbo.Documents, the data being inserted are displayed, but I can't find the table under either master or the TSQL2012 database. I checked 'Table' folder under TSQL2012. Under the master db, there was no 'Tables' folder. I see temporarydb folder. Please advise me where the table is created. Thanks! –  user2061466 Dec 31 '13 at 4:47
    
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. –  Kin Dec 31 '13 at 4:48

4 Answers 4

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.

share|improve this answer
    
Hello Thomas, I checked the database: master, there was no Documents table. I created the table under the dbo schema. I can insert rows into the table, But I can't find the table under either master or TSQL2012 database. –  user2061466 Dec 31 '13 at 4:41

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 
share|improve this answer

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'''
share|improve this answer
    
Hello Katherine, thanks for the reply. I was able to find the location for Documents table. For some reason, it didn't show up under the TSQL2012 database. Now it is displayed in the database. –  user2061466 Dec 31 '13 at 22:37

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.