Create Table and Alter table:

Hi All,

I am using SQl server studio management to create table.

How to set two attributes as a primary key: composite key. like ( proj_id, emp id) both as primary key.

How to specify the forign key constraint. using alter table

Please give me the example: don't syntax which msdn gives.

Thanks and Regards

Abdul M.G

Hi,

Look at this.

1CREATE TABLE Menu2(3 MenuIdint,4 Titlevarchar(50),5 Urlvarchar(256),6 ParentIdintNULL7)89ALTER TABLE MenuADD CONSTRAINT MenuIdPRIMARY KEY1011ALTER TABLE MenuADD CONSTRAINT ParentIdREFERENCES Menu(MenuId)

sql

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
Thanks for your help
Patrick
On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to
desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> > theprocedureexists...
> > AsCREATEPROCEDUREcan not be combined with any other Transact-SQL
> > statement in a single batch, I was wondering if there were any way to
> > achieve something like this :
> > (..)
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create Database With Attach in Restricted_user mode?

Try attaching the db and then use "alter database" and set the option
"RESTRICTED_USER".
AMB
"RBarryYoung@.gmail.com" wrote:

> How can I attach a database and keep it in Restricted_User mode? I
> cannot find a way to do this in BOL.
> -- RBarryYoung
>How can I attach a database and keep it in Restricted_User mode? I
cannot find a way to do this in BOL.
-- RBarryYoung|||Try attaching the db and then use "alter database" and set the option
"RESTRICTED_USER".
AMB
"RBarryYoung@.gmail.com" wrote:

> How can I attach a database and keep it in Restricted_User mode? I
> cannot find a way to do this in BOL.
> -- RBarryYoung
>|||No, this leaves a window of oppurtunity between the attach and the
"Alter Database". I need to "keep it" in Restricted mode, as it was
when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
for RETORE operations.
It is not supposed to be accessible by anyone until I am done making
the necessary changes to it.
Alejandro Mesa wrote:[vbcol=seagreen]
> Try attaching the db and then use "alter database" and set the option
> "RESTRICTED_USER".
>
> AMB
>
> "RBarryYoung@.gmail.com" wrote:
>|||No, this leaves a window of oppurtunity between the attach and the
"Alter Database". I need to "keep it" in Restricted mode, as it was
when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
for RETORE operations.
It is not supposed to be accessible by anyone until I am done making
the necessary changes to it.
Alejandro Mesa wrote:[vbcol=seagreen]
> Try attaching the db and then use "alter database" and set the option
> "RESTRICTED_USER".
>
> AMB
>
> "RBarryYoung@.gmail.com" wrote:
>|||<RBarryYoung@.gmail.com> wrote in message
news:1150144741.478126.196290@.f6g2000cwb.googlegroups.com...
> No, this leaves a window of oppurtunity between the attach and the
> "Alter Database". I need to "keep it" in Restricted mode, as it was
> when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
> for RETORE operations.
> It is not supposed to be accessible by anyone until I am done making
> the necessary changes to it.
Haven't tried this, but wrap the whole thing in a transaction?

> Alejandro Mesa wrote:
>|||<RBarryYoung@.gmail.com> wrote in message
news:1150144741.478126.196290@.f6g2000cwb.googlegroups.com...
> No, this leaves a window of oppurtunity between the attach and the
> "Alter Database". I need to "keep it" in Restricted mode, as it was
> when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
> for RETORE operations.
> It is not supposed to be accessible by anyone until I am done making
> the necessary changes to it.
Haven't tried this, but wrap the whole thing in a transaction?

> Alejandro Mesa wrote:
>