SQLServerのSQLの書き方

# テーブル作成
```sql
DECLARE @tablecheck int = 0
DECLARE @tablename varchar(256)='test'
DECLARE @schemaname varchar(256)='dbo'
SET @tablecheck =(
    SELECT count(1)
  FROM SYS.TABLES
  WHERE NAME = @tablename AND
  SCHEMA_ID = SCHEMA_ID(@schemaname)
)
IF  @tablecheck = 0
 
BEGIN
 
  CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [product_id] [int] NOT NULL,
    [name] [varchar](256) NOT NULL,
    [year] [int] NOT NULL,
    [amount]  [DECIMAL] (10, 2) NULL,
    [delete_flg] [bit] NOT NULL DEFAULT 0,
    [created_on] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [created_by] [varchar](256) NOT NULL DEFAULT 'system_user',
    [modified_on] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [modified_by] [varchar](256) NOT NULL DEFAULT 'system_user'
  );
  
  -- PKを追加
  ALTER TABLE [dbo].[test] ADD  CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED (id);
  -- FKを追加
  ALTER TABLE [dbo].[test]  WITH CHECK ADD  CONSTRAINT [FK_product_id] FOREIGN KEY([product_id])
  REFERENCES [dbo].[product_master] ([id]);
 
END
```

# View作成
```sql
CREATE OR ALTER VIEW [dbo].[test_view]
AS
SELECT 
     a.id,
	 b.id
FROM 
	dbo.test AS a
INNER JOIN dbo.product_master AS b
    ON a.product_id = b.id

```

# カラム追加
```sql
ALTER TABLE dbo.test ADD created_on datetime NOT NULL DEFAULT GETDATE() WITH VALUES,created_by VARCHAR(256) NOT NULL DEFAULT 'test@test.jp', modified_on datetime NOT NULL DEFAULT GETDATE() WITH VALUES,modified_by VARCHAR(256) NOT NULL DEFAULT 'test@test.jp';

ALTER TABLE dbo.test ADD delete_flg BIT NOT NULL DEFAULT 0;
```

# カラム削除
```sql
ALTER TABLE [dbo].[test] DROP COLUMN amount;
```
# 外部キー制約の一覧を取得する
```sql
SELECT
	fs.name AS '外部キー制約の名前',
	s.name + '.' + t.name AS '外部キー制約を設定しているスキーマ名とテーブル名'
FROM
	sys.foreign_keys fs
INNER JOIN sys.tables t
	ON t.object_id = fs.parent_object_id
INNER JOIN sys.schemas s
	ON s.schema_id = t.schema_id
ORDER BY
	外部キー制約の名前
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/368087/a1ae7e6e-c7d7-4945-aa3c-ce9d7a140ecf.png)


# FK削除
```
ALTER TABLE [dbo].[test] DROP CONSTRAINT FK_product_id;
```

# デフォルト制約の一覧を取得する 
```sql
SELECT
	sdc.name AS 'デフォルト制約名',
	s.name + '.' + t.name AS 'デフォルト制約を設定しているスキーマ名とテーブル名'
FROM
	sys.default_constraints sdc
INNER JOIN sys.tables t
	ON t.object_id = sdc.parent_object_id
INNER JOIN sys.schemas s
	ON s.schema_id = t.schema_id
ORDER BY
	デフォルト制約名

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/368087/85b35de7-05bb-48e5-863e-a756620db1ba.png)

# Default制約削除
```sql
ALTER TABLE dbo.test DROP CONSTRAINT DF__delete_flg__17C286CF;
```
# データ追加
```sql
INSERT INTO [dbo].[test]  VALUES ('1',N'test',N'test',N'',N'',0,0,CURRENT_TIMESTAMP,'test@test.jp',CURRENT_TIMESTAMP,'test@test.jp');

```
# データ更新
```sql
UPDATE [dbo].[test] Set name = 'test'  WHERE email='test@test.jp';

```
# データ削除
```sql
DELETE FROM [dbo].[test] WHERE id  IN ('1','2');

```



# 参考サイト
https://sqlserver.work/2023/04/01/%E3%80%90sql-server%E3%80%91%E3%83%87%E3%83%95%E3%82%A9%E3%83%AB%E3%83%88%E5%88%B6%E7%B4%84%E3%81%AE%E4%B8%80%E8%A6%A7%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B/

https://sqlserver.work/2021/01/06/%E3%80%90sys-foreign_keys-%E3%80%91%E5%A4%96%E9%83%A8%E3%82%AD%E3%83%BC%E5%88%B6%E7%B4%84%E3%81%AE%E4%B8%80%E8%A6%A7%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B/