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 = '
/* --==== DROP ====-- */
ALTER TABLE < Tabel >
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 >
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 >
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 >
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 >
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 >
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 = '
Functie
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_NAME = '
Geen opmerkingen:
Een reactie posten