I can drop a table, procedure, views if it exists using the following code but do not know how to do the same with a constraints(foreign key, check constraint):
IF EXISTS (SELECT 1 FROM sysobjects WHERE user = 'owner' and name = 'tablename' AND type = 'U')DROP TABLE owner.tablename
go
I have tried with following code -
- alter table dbname.owner.tablename drop constraint Fk_name FOREIGN
KEY (References-colname)
- if exists (select 1 from syscolumns
where id = object_id("some_table")
and name = "some_column")
begin
alter table some_table drop some_column
end
- IF EXISTS (SELECT * FROM sysobjects WHERE user = 'owner' and name = 'FK_name' AND type = 'RI')
ALTER TABLE owner.tablename DROP CONSTRAINT [owner.Fk_name]
GO
Here is my DDL generated from ddlgen command -
DROP TABLE fin_code2
go
DROP TABLE Student1
go
DROP TABLE SalesOrders
go
DROP TABLE DEPT
go
DROP TABLE library_books
go
DROP TABLE brand
go
DROP TABLE EMPLOYEE
go
DROP TABLE DEPT_test
go
DROP TABLE EMPLOYEE
go
DROP TABLE EMPLOYEE24
go
DROP TRIGGER reminder
go
DROP VIEW sysquerymetrics
go
DROP VIEW emp_dept
go
DROP VIEW empview
go
DROP PROCEDURE showdept
go
USE master
go
PRINT "<<<< CREATE DATABASE geetextract>>>>"
go
IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases
WHERE name = 'geetextract')
DROP DATABASE geetextract
go
IF (@@error != 0)
BEGIN
PRINT "Error dropping database 'geetextract'"
SELECT syb_quit()
END
go
CREATE DATABASE geetextract
ON master = '6M' -- 1536 pages
WITH DURABILITY = FULL
, DML_LOGGING = FULL
go
ALTER DATABASE geetextract
ON master = '10240M' -- 2621440 pages
go
use geetextract
go
exec sp_changedbowner 'sa', true
go
exec master.dbo.sp_dboption geetextract, 'ddl in tran', true
go
checkpoint
go
-----------------------------------------------------------------------------
-- DDL for User 'geetanjali'
-----------------------------------------------------------------------------
print '<<<<< CREATING User - "geetanjali" >>>>>'
go
exec sp_adduser 'geetanjali' ,'geetanjali' ,'public'
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.DEPT'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.DEPT" >>>>>'
go
use geetextract
go
setuser 'geetanjali'
go
create table DEPT (
DeptNo int not null,
DeptName varchar(20) not null,
Mgr int not null,
PRIMARY KEY CLUSTERED ( DeptNo ) on 'default'
)
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Index 'DeptNoind'
-----------------------------------------------------------------------------
print '<<<<< CREATING Index - "DeptNoind" >>>>>'
go
create nonclustered index DeptNoind
on geetextract.geetanjali.DEPT(DeptNo)
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.dbo.DEPT_test'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.dbo.DEPT_test" >>>>>'
go
setuser 'dbo'
go
create table DEPT_test (
DeptNo int not null,
DeptName varchar(20) not null,
Mgr int not null
)
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.EMPLOYEE'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.EMPLOYEE" >>>>>'
go
setuser 'geetanjali'
go
set quoted_identifier on
go
create table EMPLOYEE (
EmpNo int not null,
DeptNo int not null,
LastName varchar(20) not null,
FirstName varchar(20) not null,
Salary int not null,
Description text null,
PRIMARY KEY CLUSTERED ( EmpNo ) on 'default',
CONSTRAINT valid_check CHECK (Salary > 10000))
lock allpages
with dml_logging = full
on 'default'
go
sp_placeobject 'default', 'geetanjali.EMPLOYEE.tEMPLOYEE'
go
setuser
go
set quoted_identifier off
go
-----------------------------------------------------------------------------
-- DDL for Trigger 'geetextract.geetanjali.reminder'
-----------------------------------------------------------------------------
print '<<<<< CREATING Trigger - "geetextract.geetanjali.reminder" >>>>>'
go
setuser 'geetanjali'
go
create trigger geetanjali.reminder on geetanjali.EMPLOYEE for insert, update as print "Don't forget to print a report for accounting."
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.dbo.EMPLOYEE'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.dbo.EMPLOYEE" >>>>>'
go
setuser 'dbo'
go
create table EMPLOYEE (
EmpNo int not null,
DeptNo int not null,
LastName varchar(20) not null,
FirstName varchar(20) not null,
Salary int not null,
Description text null,
CONSTRAINT valid_check CHECK (Salary > 10000))
lock allpages
with dml_logging = full
on 'default'
go
sp_placeobject 'default', 'dbo.EMPLOYEE.tEMPLOYEE'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.dbo.EMPLOYEE24'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.dbo.EMPLOYEE24" >>>>>'
go
setuser 'dbo'
go
create table EMPLOYEE24 (
EmpNo int not null,
DeptNo int not null,
LastName varchar(20) not null,
FirstName varchar(20) not null,
Salary int not null,
Description text null,
CONSTRAINT valid_check23 CHECK (Salary > 10000))
lock allpages
with dml_logging = full
on 'default'
go
sp_placeobject 'default', 'dbo.EMPLOYEE24.tEMPLOYEE24'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.SalesOrders'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.SalesOrders" >>>>>'
go
setuser 'geetanjali'
go
create table SalesOrders (
FinancialCode char(2) not null,
CustomerID int not null,
History char(100) not null,
OrderDate date not null,
ID bigint not null,
PRIMARY KEY CLUSTERED ( ID ) on 'default'
)
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.Student1'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.Student1" >>>>>'
go
setuser 'geetanjali'
go
set quoted_identifier on
go
create table Student1 (
StudentId int not null,
Name char(100) not null,
Class char(50) not null,
School char(100) not null,
PRIMARY KEY CLUSTERED ( StudentId ) on 'default',
UNIQUE NONCLUSTERED ( StudentId, Name ) on 'default',
CONSTRAINT test_const CHECK (StudentId > 0))
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
set quoted_identifier off
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.brand'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.brand" >>>>>'
go
setuser 'geetanjali'
go
set quoted_identifier on
go
create table brand (
code char(8) not null,
valid int not null,
rowid numeric(10,0) not null,
CONSTRAINT brand_pk PRIMARY KEY CLUSTERED ( code ) on 'default',
CONSTRAINT brand_is_valid UNIQUE NONCLUSTERED ( code, valid ) on 'default',
CONSTRAINT valid_check1 CHECK (valid IN (0,1)))
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
set quoted_identifier off
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.fin_code2'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.fin_code2" >>>>>'
go
setuser 'geetanjali'
go
create table fin_code2 (
code int not null,
type char(10) not null,
description char(235) not null,
id bigint not null,
UNIQUE NONCLUSTERED ( code ) on 'default'
)
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Table 'geetextract.geetanjali.library_books'
-----------------------------------------------------------------------------
print '<<<<< CREATING Table - "geetextract.geetanjali.library_books" >>>>>'
go
setuser 'geetanjali'
go
create table library_books (
isbn char(20) not null,
copyright_date date not null,
title char(100) not null,
author char(50) not null,
PRIMARY KEY CLUSTERED ( isbn ) on 'default'
)
lock allpages
with dml_logging = full
on 'default'
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Index 'au_id_ind'
-----------------------------------------------------------------------------
print '<<<<< CREATING Index - "au_id_ind" >>>>>'
go
create nonclustered index au_id_ind
on geetextract.geetanjali.library_books(isbn)
go
-----------------------------------------------------------------------------
-- DDL for View 'geetextract.geetanjali.emp_dept'
-----------------------------------------------------------------------------
print '<<<<< CREATING View - "geetextract.geetanjali.emp_dept" >>>>>'
go
setuser 'geetanjali'
go
set quoted_identifier on
go
create view emp_dept AS SELECT EmpNo, DEPT.DeptNo FROM EMPLOYEE JOIN DEPT ON EMPLOYEE.DeptNo = DEPT.DeptNo
go
set quoted_identifier off
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for View 'geetextract.geetanjali.empview'
-----------------------------------------------------------------------------
print '<<<<< CREATING View - "geetextract.geetanjali.empview" >>>>>'
go
setuser 'geetanjali'
go
set quoted_identifier on
go
create view empview (FirstName) as select distinct FirstName from EMPLOYEE
go
set quoted_identifier off
go
setuser
go
-----------------------------------------------------------------------------
-- DDL for Stored Procedure 'geetextract.geetanjali.showdept'
-----------------------------------------------------------------------------
print '<<<<< CREATING Stored Procedure - "geetextract.geetanjali.showdept" >>>>>'
go
setuser 'geetanjali'
go
set quoted_identifier on
go
CREATE PROCEDURE showdept @deptname varchar(30) AS SELECT EMPLOYEE.EmpNo FROM EMPLOYEE, DEPT WHERE DEPT.DeptNo = EMPLOYEE.DeptNo
go
sp_procxmode 'showdept', unchained
go
set quoted_identifier off
go
setuser
go
-----------------------------------------------------------------------------
-- Dependent DDL for Object(s)
-----------------------------------------------------------------------------
use geetextract
go
sp_addthreshold geetextract, 'logsegment', 24, sp_thresholdaction
go
Grant Select on dbo.sysobjects(name,id,uid,type,userstat,sysstat,indexdel,schemacnt,sysstat2,crdate,expdate,deltrig,instrig,updtrig,seltrig,ckfirst,cache,objspare,versionts,loginame,identburnmax,spacestate,erlchgts,sysstat3) to public
go
Grant Select on dbo.sysindexes to public
go
Grant Select on dbo.syscolumns to public
go
Grant Select on dbo.systypes to public
go
Grant Select on dbo.sysprocedures to public
go
Grant Select on dbo.syscomments to public
go
Grant Select on dbo.syssegments to public
go
Grant Select on dbo.syslogs to public
go
Grant Select on dbo.sysprotects to public
go
Grant Select on dbo.sysusers to public
go
Grant Select on dbo.sysalternates to public
go
Grant Select on dbo.sysdepends to public
go
Grant Select on dbo.syskeys to public
go
Grant Select on dbo.sysusermessages to public
go
Grant Select on dbo.sysreferences to public
go
Grant Select on dbo.sysconstraints to public
go
Grant Select on dbo.systhresholds to public
go
Grant Select on dbo.sysroles to public
go
Grant Select on dbo.sysattributes to public
go
Grant Select on dbo.sysslices to public
go
Grant Select on dbo.systabstats to public
go
Grant Select on dbo.sysstatistics to public
go
Grant Select on dbo.sysxtypes to public
go
Grant Select on dbo.sysjars to public
go
Grant Select on dbo.sysqueryplans to public
go
Grant Select on dbo.syspartitions to public
go
Grant Select on dbo.syspartitionkeys to public
go
alter table geetextract.geetanjali.EMPLOYEE
add constraint EMPLOYEE_DeptNo_896003192 FOREIGN KEY (DeptNo) REFERENCES geetextract.geetanjali.DEPT(DeptNo)
go
alter table geetextract.geetanjali.fin_code2
add constraint fin_code2_id_800002850 FOREIGN KEY (id) REFERENCES geetextract.geetanjali.SalesOrders(ID)
go
alter table geetextract.geetanjali.EMPLOYEE drop constraint EMPLOYEE_DeptNo_896003192 FOREIGN KEY (DeptNo)
go
alter table geetextract.geetanjali.fin_code2 drop constraint fin_code2_id_800002850 FOREIGN KEY (id)
go
alter table geetextract.geetanjali.EMPLOYEE add CONSTRAINT valid_check CHECK (Salary > 10000)
go
alter table geetextract.geetanjali.EMPLOYEE drop constraint valid_check
go
alter table geetextract.geetanjali.Student1 add CONSTRAINT test_const CHECK (StudentId > 0)
go
alter table geetextract.geetanjali.Student1 drop constraint test_const
go
alter table geetextract.geetanjali.brand add CONSTRAINT valid_check1 CHECK (valid IN (0,1))
go
alter table geetextract.geetanjali.brand drop constraint valid_check1
go
alter table geetanjali.EMPLOYEE DISABLE TRIGGER geetanjali.reminder
go
alter table geetanjali.EMPLOYEE ENABLE TRIGGER geetanjali.reminder
go
- it will execute but not dropping that key.
