Create Table in Specified Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Table in Specified Database

Author

Topic

peterhw
Starting Member

12 Posts

Posted - 2012-06-09 : 09:26:49

I am new to SQL and using SQL Server 2012 Studio.
I am also generating VBA code in EXCEL to run with SQL.

I created a new database called Test by right clicking on Databases and add new one. Similarly I created a new table. No problem all worked fine. I can see a database called Test and my new table.

If I run


CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);

It Executes but I can't find the Table.
I have now used the USE command to create in a specified database and that works but I still can't find the first table created. I can't find a Tables section (other than my Test database)

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-09 : 11:15:30

Is it possible the it was created in another database?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 11:24:11

quote:
Originally posted by peterhw

I am new to SQL and using SQL Server 2012 Studio.
I am also generating VBA code in EXCEL to run with SQL.

I created a new database called Test by right clicking on Databases and add new one. Similarly I created a new table. No problem all worked fine. I can see a database called Test and my new table.

If I run

CREATE TABLE OrderItems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL
);

It Executes but I can't find the Table.
I have now used the USE command to create in a specified database and that works but I still can't find the first table created. I can't find a Tables section (other than my Test database)

it may be that you've created it in master database.

try running this and see what it returns


EXEC sp_Msforeachdb 'IF EXISTS(SELECT 1 FROM ?.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''OrderItems'') SELECT ''?'''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peterhw
Starting Member

12 Posts

Posted - 2012-06-11 : 05:22:06

The Query returns 'master'.

I assume this TABLE (and others) are in the 'master' database but I just can't locate them i.e. there is nothing called Tables under the Master database (there is a Tables entry under my Test database)

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-11 : 10:34:36

Try this sql code:
use master
GO
select * from sys.sysobjects where xtype = 'U'order by name

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

peterhw
Starting Member

12 Posts

Posted - 2012-06-11 : 11:52:34

Thanks

Yes - that appears to list them but no still idea where to find them.

It doesn't really matter but I created these very early on and i9n practice I should have created in a new database 'Order_Processing' or similar and I would like to delete the originals

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:12:32

quote:
Originally posted by peterhw

Thanks

Yes - that appears to list them but no still idea where to find them.

It doesn't really matter but I created these very early on and i9n practice I should have created in a new database 'Order_Processing' or similar and I would like to delete the originals

just expand system databases master and expand tables folder within them to find required tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-12 : 00:42:32

If you can view them via the SELECT statement - then you can issue a DROP TABLE command. Assuming you have the permissions

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

peterhw
Starting Member

12 Posts

Posted - 2012-06-12 : 02:26:40

quote:

just expand system databases master and expand tables folder within them to find required tables

That's what I'd expect to be able to do .... but there is no 'Tables' or 'Database Diagrams' folders listed under Master (or other system databases)

peterhw
Starting Member

12 Posts

Posted - 2012-06-12 : 02:36:50

quote:
Originally posted by jackv

If you can view them via the SELECT statement - then you can issue a DROP TABLE command. Assuming you have the permissions

Many thanks - worked no problem - the Tables were not visible in the 'Object Explorer' window under master (see last reply / post)

peterhw
Starting Member

12 Posts

Posted - 2012-06-12 : 02:42:55

Many thanks for for help

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-12 : 11:07:50

Not a problem. Strange you can't expand the Object Explorer - have you thought of installing a later version of SSMS?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

peterhw
Starting Member

12 Posts

Posted - 2012-06-12 : 11:18:07

Thanks again.
I have only just installed the 'system' i.e. SQL Server 2012 with following components
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 4.0.30319.269
Operating System 6.1.7601

Don't know what SSMS is or how to update (or if needs to do so)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-12 : 11:38:05

ssms = sql server management studio.

Sometimes when you create a new table the GUI doesn't refresh. You can right click on the db or the tables folder in the db and click on refresh.

Also you should see a toolbar on the top of SSMS by default that shows you which db you are running commands in.

Stay out of master.

=== Transact Charlie ==============================

   '''; DROP DATABASE Problems; -- '

http://nosqlsolution.blogspot.co.uk/[/code]

peterhw
Starting Member

12 Posts

Posted - 2012-06-13 : 03:43:09

quote:
Originally posted by Transact Charlie

ssms = sql server management studio.

No need to update / upgrade then - I assume.

quote:

Sometimes when you create a new table the GUI doesn't refresh. You can right click on the db or the tables folder in the db and click on refresh.

The original databases were loaded on my very first entry into SQL with a series of scripts to CREATE tables and POPULATE tables. I have been in/out of ssms( I remembered!) and rebooted since the original installation. I subsequently created my Test database and wanted to remove the original tables and install in Test.

quote:

Also you should see a toolbar on the top of SSMS by default that shows you which db you are running commands in.

Stay out of master.

Yes can see the Toolbar and now want to keep well away from Master !

lisayling
Starting Member

5 Posts

Posted - 2012-06-13 : 05:38:45

en, i can accept this view, but not the fully
unspammed
lisayling

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources