SQL Objects (Syntax Code)

Als je (wijzing)scripts maakt op een bestaand datamodel, dan zal je vaak code nodig hebben waarin controles moeten plaatsvinden op bijvoorbeeld de aanwezigheid van objecten. Hieronder een overzichtje van syntax die je vaak nodig hebt:

Tabel Syntax
Tabel
Kolom
Foreign Key
Primary Key
Unique Key
Check Constraint
Default Constraint
Trigger

Routine Syntax
Stored Procedure
Functie


Tabel
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects WHERE Type = 'U' AND Name = '<Tabel>')

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '<Tabel>' AND TABLE_SCHEMA = '<Schema>')

/* --==== DROP ====-- */
DROP TABLE < Tabel >

Kolom
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1
           FROM Sys.Objects sO
                INNER JOIN Sys.Columns sC ON sO.ID = sC.ID
           WHERE sO.XType = 'U' AND sC.Name = '<Kolom>' AND sO.Name = '<Tabel>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = '<Schema>' AND TABLE_NAME = '' AND COLUMN_NAME = '<Kolom>')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP COLUMN < Kolom >


Foreign Key
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE Parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'F' AND Name = '<Foreignkey>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Foreignkey>' AND TABLE_NAME = '<Tabel>' AND CONSTRAINT_TYPE = 'FOREIGN KEY')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Primary Key
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'PK' AND Name = '<Primarykey>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Primarykey>' AND TABLE_NAME = '<Tabel>' 
           AND CONSTRAINT_TYPE = 'PRIMARY KEY')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Unique Key
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'UQ' AND Name = '<Uniquekey>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Uniquekey >' AND TABLE_NAME = '<Tabel>'
           AND CONSTRAINT_TYPE = 'UNIQUE')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Check Constraint
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'C' AND Name = '<Checkconstraint>')
 
IF EXISTS (SELECT 1 INFORMATION_SCHEMA.CHECK_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Checkconstraint>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Checkconstraint>' AND TABLE_NAME = '<Tabel>'
           AND CONSTRAINT_TYPE = 'CHECK')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Default Constraint
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID(< Tabel >) AND Type = 'D' AND Name = '<DefaultConstraint>')


/* --==== DROP ====-- */
--Default niet aan kolom gebonden
DROP DEFAULT < DefaultConstraint >


--Default aan de kolom gebonden
EXEC sp_unbindefault '< Tabel >'.'< Kolom >'
DROP DEFAULT < DefaultConstraint >


Trigger
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'TR' AND Name = '<Trigger>')


/* --==== DROP ====-- */
DROP TRIGGER < Trigger >
 Stored Procedure

/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = '' AND SPECIFIC_SCHEMA = '')


Functie
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_NAME = '' AND SPECIFIC_SCHEMA = '')