Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
/*-- Drop Foreign Keys Begin Created -------------------------------------------------------------*/ /* Example if OBJECT_ID('SchemaName.FK_OnTable_AgainstTable') is not null alter table OnTable drop constraint [FK_OnTable_AgainstTable] */ /*-- Drop Tables Being Created -------------------------------------------------------------------*/ /* Example if object_id('SchemaName.TableName') is not null drop table SchemaName.TableName */ /*-- Drop Column Constraints Being Created -------------------------------------------------------*/ /* Example */ /*-- Drop Columns Being Created ------------------------------------------------------------------*/ /* Example if COL_LENGTH('TableName', 'ColumnName') is not null alter table dbo.TableName drop column ColumnName */ /*-- Drop/Recreate Schemas -----------------------------------------------------------------------*/ /* Example if exists (select 2 from sys.schemas where name = 'SchemaName') drop schema SchemaName go create schema SchemaName authorization dbo go */ /*-- Create Tables and Add Columns ---------------------------------------------------------------*/ /* Examples create table dbo.TableName ( ColumnName int not null identity(1,1) ,ColumnName2 varchar(30) not null ,CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ( [ColumnName] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ------------------- alter table dbo.TableName ADD ColumnName int not null identity(1,1) ,ColumnName2 date not null ,ColumnName3 varchar(3) null constraint DF_TableName_ColumnName3 DEFAULT 'XYZ' WITH VALUES */ /*-- Create Foreign Keys -------------------------------------------------------------------------*/ /* Example ALTER TABLE OnSchema.OnTable WITH CHECK ADD CONSTRAINT [FK_OnTable_AgainstTable] FOREIGN KEY (OnColumn) REFERENCES AgainstSchema.AgainstTable (AgainstColumn) */ /*-- Create Column Constraints -------------------------------------------------------------------*/ /*-- Populate Lookup Tables ----------------------------------------------------------------------*/ /*-- Make Populated Columns Non-Nullable ---------------------------------------------------------*/ /* alter table Schema.Table alter column ColumnName DataType not null */ /*-- Drop/Recreate Indexes -----------------------------------------------------------------------*/ /* Example IF EXISTS(SELECT 2 FROM sysindexes si INNER JOIN sysobjects so ON so.id = si.id WHERE si.[Name] = N'IX_TableName_ColumnName' AND so.[Name] = N'TableName') BEGIN DROP INDEX [IX_TableName_ColumnName] ON [dbo].[TableName] END GO CREATE INDEX [IX_TableName_ColumnName] ON [dbo].[TableName] ( [ColumnName] ASC ) GO */
if OBJECT_ID('SchemaName.FK_OnTable_AgainstTable') is not null alter table OnTable drop constraint [FK_OnTable_AgainstTable]
if object_id('SchemaName.TableName') is not null drop table SchemaName.TableName
if exists (select * from sys.objects where name = 'DF_SchemaName_TableName_ColumnName') alter table SchemaName.TableName drop constraint DF_SchemaName_TableName_ColumnName if exists (select * from sys.objects where name = 'CK_SchemaName_TableName_ConstraintName') alter table SchemaName.TableName drop constraint DF_SchemaName_TableName_ConstraintName
if COL_LENGTH('TableName', 'ColumnName') is not null alter table dbo.TableName drop column ColumnName
if exists (select 2 from sys.schemas where name = 'SchemaName') drop schema SchemaName go create schema SchemaName authorization dbo go
create table dbo.TableName ( ColumnName int not null identity(1,1) ,ColumnName2 varchar(30) not null ,CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ( [ColumnName] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
alter table dbo.TableName ADD ColumnName int not null identity(1,1) ,ColumnName2 date not null ,ColumnName3 varchar(3) null constraint DF_TableName_ColumnName3 DEFAULT 'XYZ' WITH VALUES
CREATE TABLE
ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED (TableNameID ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE OnSchema.OnTable WITH CHECK ADD CONSTRAINT [FK_OnTable_AgainstTable] FOREIGN KEY (OnColumn) REFERENCES AgainstSchema.AgainstTable (AgainstColumn)
ALTER TABLE SchemaName.TableName WITH CHECK ADD CONSTRAINT CK_SchemaName_TableName_ConstraintName CHECK BooleanExpression GO ALTER TABLE SchemaName.TableName CHECK CONSTRAINT CK_SchemaName_TableName_ConstraintName GO
IF EXISTS(SELECT 2 FROM sysindexes si INNER JOIN sysobjects so ON so.id = si.id WHERE si.[Name] = N'IX_TableName_ColumnName' AND so.[Name] = N'TableName') BEGIN DROP INDEX [IX_TableName_ColumnName] ON [dbo].[TableName] END GO CREATE INDEX [IX_TableName_ColumnName] ON [dbo].[TableName] ( [ColumnName] ASC ) GO
COLUMN
ADD
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
ALTER TABLE MyTable ADD AddDate smalldatetime NOT NULL CONSTRAINT AddDateDflt DEFAULT getdate()
ALTER TABLE MyTable ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT getdate() WITH VALUES
ALTER TABLE doc_exb DROP COLUMN column_b
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
ALTER TABLE dbo.MyTable WITH CHECK ADD CONSTRAINT CK_MyTable_Xor CHECK ( ( ( ( (case when [OptionId] IS NOT NULL then (1) else (0) end +case when [NumericValue] IS NOT NULL then (1) else (0) end) +case when [DateValue] IS NOT NULL then (1) else (0) end) +case when [PrecisionValue] IS NOT NULL then (1) else (0) end)=(1))) GO ALTER TABLE dbo.MyTable CHECK CONSTRAINT CK_MyTable_Xor GO
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.