Where is my table created...

Jun Du

I have a couple of newbie questions for SQL Server. On the SQL Server Management Studio, I used the following script to delete and create a table on the master databse:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PARTNOTE]
GO

CREATE TABLE [dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I can run this as many times as I want. Both deletion and creation work well. My first question is "After the creation, where is the table creates?" I couldn't find where the table object from Object Explorer. Now I modify the script to perform the same tasks not on the master, but on "MyDatabase":

if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyDatabase].[dbo].[PARTNOTE]
GO

CREATE TABLE [MyDatabase].[dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The creation still works but the deletion does not. It couldn't find where the table was creates. Could someone please explain what goes wrong in the modified script? Thanks!

Best, Jun

Blue_Boy

After you execute script, refresh Tables node by right click on it and click refresh, it should shows created tables.

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com

Jun Du

Thanks. The table created shows up after the refreshing. Another question. My modified script

if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [MyDatabase].[dbo].[WL_HEADU]
GO

cannot detect the existence of the table I created. If I just execute

drop table [MyDatabase].[dbo].[WL_HEADU]
GO

it works well. What is a proper query I should make in order to detect the table created in "MyDatabase"?

Best, Jun

Jun Du

Figured it out...adding

USE [MyDatabase]
GO

before the query can detect the table created.

Best, Jun

Corporal Agarn

Look to the if statement. Check what

select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1

gives you when the file is known to exist. I have 2008 and it uses sys.object and I do not remember how to check for exists from prior.

SilimSayo

The table is on the cloud :)

Blue_Boy

That's great :)

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com

jschell

Jun Du wrote:

create a table on the master databse:

You need to learn to create your own database and then tables go in that. And once you do delete everything that you created in 'master'.