Created
September 3, 2019 01:11
-
-
Save Camilotk/56f2ae8686387a19da5a1b3f712282fa to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [master] | |
GO | |
/****** Object: Database [Pets] Script Date: 02/09/2019 14:49:34 ******/ | |
CREATE DATABASE [Pets] | |
CONTAINMENT = NONE | |
ON PRIMARY | |
( NAME = N'Pets', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.HAPPY\MSSQL\DATA\Pets.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) | |
LOG ON | |
( NAME = N'Pets_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.HAPPY\MSSQL\DATA\Pets_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) | |
GO | |
ALTER DATABASE [Pets] SET COMPATIBILITY_LEVEL = 140 | |
GO | |
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) | |
begin | |
EXEC [Pets].[dbo].[sp_fulltext_database] @action = 'enable' | |
end | |
GO | |
ALTER DATABASE [Pets] SET ANSI_NULL_DEFAULT OFF | |
GO | |
ALTER DATABASE [Pets] SET ANSI_NULLS OFF | |
GO | |
ALTER DATABASE [Pets] SET ANSI_PADDING OFF | |
GO | |
ALTER DATABASE [Pets] SET ANSI_WARNINGS OFF | |
GO | |
ALTER DATABASE [Pets] SET ARITHABORT OFF | |
GO | |
ALTER DATABASE [Pets] SET AUTO_CLOSE OFF | |
GO | |
ALTER DATABASE [Pets] SET AUTO_SHRINK OFF | |
GO | |
ALTER DATABASE [Pets] SET AUTO_UPDATE_STATISTICS ON | |
GO | |
ALTER DATABASE [Pets] SET CURSOR_CLOSE_ON_COMMIT OFF | |
GO | |
ALTER DATABASE [Pets] SET CURSOR_DEFAULT GLOBAL | |
GO | |
ALTER DATABASE [Pets] SET CONCAT_NULL_YIELDS_NULL OFF | |
GO | |
ALTER DATABASE [Pets] SET NUMERIC_ROUNDABORT OFF | |
GO | |
ALTER DATABASE [Pets] SET QUOTED_IDENTIFIER OFF | |
GO | |
ALTER DATABASE [Pets] SET RECURSIVE_TRIGGERS OFF | |
GO | |
ALTER DATABASE [Pets] SET DISABLE_BROKER | |
GO | |
ALTER DATABASE [Pets] SET AUTO_UPDATE_STATISTICS_ASYNC OFF | |
GO | |
ALTER DATABASE [Pets] SET DATE_CORRELATION_OPTIMIZATION OFF | |
GO | |
ALTER DATABASE [Pets] SET TRUSTWORTHY OFF | |
GO | |
ALTER DATABASE [Pets] SET ALLOW_SNAPSHOT_ISOLATION OFF | |
GO | |
ALTER DATABASE [Pets] SET PARAMETERIZATION SIMPLE | |
GO | |
ALTER DATABASE [Pets] SET READ_COMMITTED_SNAPSHOT OFF | |
GO | |
ALTER DATABASE [Pets] SET HONOR_BROKER_PRIORITY OFF | |
GO | |
ALTER DATABASE [Pets] SET RECOVERY FULL | |
GO | |
ALTER DATABASE [Pets] SET MULTI_USER | |
GO | |
ALTER DATABASE [Pets] SET PAGE_VERIFY CHECKSUM | |
GO | |
ALTER DATABASE [Pets] SET DB_CHAINING OFF | |
GO | |
ALTER DATABASE [Pets] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) | |
GO | |
ALTER DATABASE [Pets] SET TARGET_RECOVERY_TIME = 60 SECONDS | |
GO | |
ALTER DATABASE [Pets] SET DELAYED_DURABILITY = DISABLED | |
GO | |
EXEC sys.sp_db_vardecimal_storage_format N'Pets', N'ON' | |
GO | |
ALTER DATABASE [Pets] SET QUERY_STORE = OFF | |
GO | |
USE [Pets] | |
GO | |
/****** Object: Table [dbo].[Owner] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[Owner]( | |
[OwnerID] [int] IDENTITY(1,1) NOT NULL, | |
[Name] [varchar](50) NULL, | |
[Phone] [varchar](50) NULL, | |
[Address] [nchar](10) NULL, | |
CONSTRAINT [PK_Owner] PRIMARY KEY CLUSTERED | |
( | |
[OwnerID] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
/****** Object: Table [dbo].[Pet] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[Pet]( | |
[PetID] [int] IDENTITY(1,1) NOT NULL, | |
[Name] [varchar](50) NULL, | |
[Specimen] [varchar](50) NULL, | |
[OwnerID] [int] NOT NULL, | |
CONSTRAINT [PK_Pet] PRIMARY KEY CLUSTERED | |
( | |
[PetID] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
ALTER TABLE [dbo].[Pet] WITH CHECK ADD CONSTRAINT [FK_Pet_Pet] FOREIGN KEY([OwnerID]) | |
REFERENCES [dbo].[Owner] ([OwnerID]) | |
GO | |
ALTER TABLE [dbo].[Pet] CHECK CONSTRAINT [FK_Pet_Pet] | |
GO | |
/****** Object: StoredProcedure [dbo].[DeleteOwnerByID] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[DeleteOwnerByID] | |
@OwnerID int | |
AS | |
BEGIN | |
DELETE FROM dbo.Owner | |
WHERE @OwnerID = dbo.Owner.OwnerID; | |
DELETE FROM dbo.Pet | |
WHERE @OwnerID = dbo.Pet.OwnerID; | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[RemovePetByID] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[RemovePetByID] | |
@PetID int | |
AS | |
BEGIN | |
DELETE FROM dbo.Pet | |
WHERE dbo.Pet.PetID = @PetID | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[ReturnAllOwners] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[ReturnAllOwners] | |
AS | |
BEGIN | |
SELECT * FROM dbo.Owner | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[ReturnOwnerByID] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[ReturnOwnerByID] | |
@OwnerID int | |
AS | |
BEGIN | |
SELECT * FROM dbo.Owner | |
WHERE dbo.Owner.OwnerID = @OwnerID; | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[ReturnOwnerPets] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[ReturnOwnerPets] | |
@OwnerID int | |
AS | |
BEGIN | |
SELECT * FROM dbo.Pet | |
WHERE dbo.Pet.OwnerID = @OwnerID | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[SaveOwner] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[SaveOwner] | |
@Name varchar(50), | |
@Phone varchar(50), | |
@Address varchar(50) | |
AS | |
BEGIN | |
INSERT INTO dbo.Owner | |
(Name, Phone, Address) | |
VALUES | |
(@Name, @Phone, @Address) | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[SavePet] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[SavePet] | |
@Name varchar(50), | |
@Specimen varchar(50), | |
@OwnerID int | |
AS | |
BEGIN | |
INSERT INTO dbo.Pet | |
(Name, Specimen, OwnerID) | |
VALUES | |
(@Name, @Specimen, @OwnerID) | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[UpdateOwner] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[UpdateOwner] | |
@OwnerID int, | |
@Name varchar(50), | |
@Phone varchar(50), | |
@Address varchar(50) | |
AS | |
BEGIN | |
UPDATE dbo.Owner | |
SET | |
dbo.Owner.Name = @Name, | |
dbo.Owner.Phone = @Phone, | |
dbo.Owner.Address = @Address | |
WHERE dbo.Owner.OwnerID = @OwnerID | |
END | |
GO | |
/****** Object: StoredProcedure [dbo].[UpdatePet] Script Date: 02/09/2019 14:49:34 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [dbo].[UpdatePet] | |
@PetID int, | |
@Name varchar(50), | |
@Specimen varchar(50), | |
@OwnerID int | |
AS | |
BEGIN | |
UPDATE dbo.Pet | |
SET | |
dbo.Pet.Name = @Name, | |
dbo.Pet.Specimen = @Specimen, | |
dbo.Pet.OwnerID = @OwnerID | |
WHERE dbo.Pet.PetID = @PetID | |
END | |
GO | |
USE [master] | |
GO | |
ALTER DATABASE [Pets] SET READ_WRITE | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment