Create table + index + primary

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE

thank youi am getting an error

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersExtra] (
[id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
) ON [PRIMARY]

Msg 1767, Level 16, State 0, Line 50
Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
Msg 1750, Level 16, State 0, Line 50|||you are trying to reference a non-existing table, 'Users.id_Users'|||but I have created it just before

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]|||To get back on the first question ...
You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

Gr,
Yveau|||Add Go and try it again|||but I have created it just beforeyes, you did

however, that is not the source of your problem

you said REFERENCES [Users].[id_Users]

this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

for the correct syntax, please see the manual

:)

CREATE SCHEMA in db A from a stored procedure in db B

Hi All

I have a SP that i create tables and other objects on another database.

Creating table work well.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE TABLE ABC (recid int)
exec (@.s)

But if i try to create a schema it gives error :

'CREATE SCHEMA' must be the first statement in a query batch.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE SCHEMA AAA
exec (@.s)

How can i solve it?

Thanks.

declare @.s nvarchar(2000)
set @.s = 'use db01'

exec (@.s)
set @.s = 'CREATE SCHEMA AAA
exec (@.s)

|||

Hi Asvin

If i use "use db01" in exec, when exec completes, it gives up using db01, so if i'm running script in db02, 'CREATE SCHEMA AAA' works on db02.

Best regards.

|||

Standard disclaimer. Generally not a good idea to be creating tables and schemas on the fly.

That out of the way, this method should work:

exec('use tempdb; exec sp_executesql N''create schema test''')

|||

You can do the following in SQL Server 2000/2005 (similar approach can be used in SQL70 also):

declare @.sp nvarchar(500)

set @.sp = quotename(N'db01') + N'sys.sp_executesql'

exec @.sp 'CREATE SCHEMA AAA....'

-- or

exec @.sp @.s