CREATE TABLE code

[GUJUm0deL]

I am using MS SQL Server 2005.

I use HostMySite as my hosting company, and last night I had them generate a .SQL script of the database. When I looked at the .SQL script, I noticed this and was wondering if this was normal or was there something wrong with the initial dB schema:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ABCD].[help]') AND type in (N'U'))
BEGIN
CREATE TABLE [ABCD].[help](
	[helpid] [int] IDENTITY(1,1) NOT NULL,
	[helptitle] [varchar](200) NULL,
	[helpcontent] [varchar](800) NULL,
 [COLOR=red] CONSTRAINT [PK_help_Temp] PRIMARY KEY CLUSTERED 
(
	[helpid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)[/color] ON [PRIMARY]
END

The above CREATE TABLE snippet appears for all tables. I wanted to know what the purpose of the portion in red is.


_____________________________
Just Imagine.

http://www.imajinarts.com

 

[SQLDenis]

that is your primary key
that is normal yes

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[GUJUm0deL]

OK, so I'm running into an issue when I try to update the column data-type. HMS has a web interface called "ASP Enterprise Manager" that allows me to make updates to the tables using IE.

When I try to update the [helptitle] [varchar](200) NULL, to [helptitle] [varchar](255) NULL, -- I get an error:

Error:   There is already an object named 'PK_help_Temp' in the database. Could not create constraint. See previous errors.
Source:   .Net SqlClient Data Provider

Why would I be getting that?

HMS is looking into the issue but I wanted a better idea about the issue so I came here.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[SQLDenis]

did you run this first?

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ABCD].[help]') AND type in (N'U'))

that will drop the table (including the constraints)
Also keep in mind that the constraint name [PK_help_Temp] is unique, you can't have 2 tables with that name

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[GUJUm0deL]

From "ASP Enterprise Manager", I do not have the ability to run

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ABCD].[help]') AND type in (N'U'))

"ASP Enterprise Manager" is a web GUI that gives me fields to update and then click the "save table" button. I'm guessing the "ASP Enterprise Manager" should run IF NOT EXISTS prior to saving the table -- I'll have to ask HMS this.

I'm dissecting the .SQL script of the database and I'm seeing things I've never seen before. Is it possible for me to send you this file so you can tell me if all is OK with the database or something odd is going on?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[SQLDenis]

From "ASP Enterprise Manager", I do not have the ability to run

then either run SSMS or Query Analyzer. When you deal with databases you use the tool that comes with it not some half-baked GUI infested solution

I'm dissecting the .SQL script of the database and I'm seeing things I've never seen before. Is it possible for me to send you this file so you can tell me if all is OK with the database or something odd is going on?

There is nothing going on here, these are regular DB generated scripts, like I said before use the tool for the job and you will be fine

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions