SQLServerの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作成

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

カラム追加

ALTER TABLE dbo.test ADD created_on datetime NOT NULL DEFAULT GETDATE() WITH VALUES,created_by VARCHAR(256) NOT NULL DEFAULT '[e-mail]', modified_on datetime NOT NULL DEFAULT GETDATE() WITH VALUES,modified_by VARCHAR(256) NOT NULL DEFAULT '[e-mail]';

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

カラム削除

ALTER TABLE [dbo].[test] DROP COLUMN amount;

外部キー制約の一覧を取得する

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
	外部キー制約の名前

FK削除

ALTER TABLE [dbo].[test] DROP CONSTRAINT FK_product_id;

デフォルト制約の一覧を取得する

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
	デフォルト制約名

Default制約削除

ALTER TABLE dbo.test DROP CONSTRAINT DF__delete_flg__17C286CF;

データ追加

INSERT INTO [dbo].[test]  VALUES ('1',N'test',N'test',N'',N'',0,0,CURRENT_TIMESTAMP,'[e-mail]',CURRENT_TIMESTAMP,'[e-mail]');

データ更新

UPDATE [dbo].[test] Set name = 'test'  WHERE email='[e-mail]';

データ削除

DELETE FROM [dbo].[test] WHERE id  IN ('1','2');

参考サイト