Script to Generate Drop / Add Constraint Queries
This article provides a way to generate DROP and ADD CONSTRAINT queries for all tables of the database.
Introduction
We face situations wherein we want to script DROP and CREATE queries of different object types in a database. Scripting all object types table-wise should be easy using the Generate Scripts wizard of SSMS. But generating the scripts for a particular object type alone proves to be elusive. This article covers one such scenario of scripting the DROP and CREATE queries of Primary keys, Foreign keys and default constraints of a database.
Foreign keys
The foreign key constraint query basically bears details of the constraint_name, parent table name, child table name and the participating columns. In addition one key aspect is to script out the constraint with respect to the is_trusted and is_enabled status flags as they decide the key feature as to whether the constraint is active or not.
This information can be obtained from the following system tables:
- sysforeignkeys
- syscolumns
Drop Foreign Key
The drop foreign key query can be generated quite simply with the help of constraint name and the parent/child table names.
---------------------------------------------
--ALTER TABLE DROP FOREIGN CONSTRAINT Queries
---------------------------------------------
SELECT distinct
'ALTER TABLE '+QUOTENAME(object_schema_name(fkeyid))+'.'+QUOTENAME(object_name(fkeyid))+
' DROP CONSTRAINT '+QUOTENAME(object_name(constid))
FROM
sys.sysforeignkeys sfk
This gives us a result as below, which when copied and executed, performs the necessary action of dropping the foreign keys:
Add Foreign key
The ADD FOREIGN KEY query can be generated by coupling the sysconstraints system table with the syscolumns table to get the parent/children table names and the corresponding column names.
------------------------------------------------
--ALTER TABLE CREATE FOREIGN CONSTRAINT Queries
------------------------------------------------
--Obtaining the necessary info from the sys tables
SELECT
constid,QUOTENAME(object_name(constid)) as constraint_name
,CASE WHEN fk.is_not_trusted=1 THEN 'WITH NOCHECK' ELSE 'WITH CHECK' END as trusted_status
,QUOTENAME(object_schema_name(fkeyid))+'.'+QUOTENAME(object_name(fkeyid)) as fk_table,QUOTENAME(c1.name) as fk_col
,QUOTENAME(object_schema_name(rkeyid))+'.'+QUOTENAME(object_name(rkeyid)) as rk_table,QUOTENAME(c2.name) as rk_col
,CASE WHEN fk.delete_referential_action=1 AND fk.delete_referential_action_desc='CASCADE' THEN 'ON DELETE CASCADE ' ELSE '' END as delete_cascade
,CASE WHEN fk.update_referential_action=1 AND fk.update_referential_action_desc='CASCADE' THEN 'ON UPDATE CASCADE ' ELSE '' END as update_cascade
,CASE WHEN fk.is_disabled=1 THEN 'NOCHECK' ELSE 'CHECK' END as check_status
--,sysfk.*,fk.*
into #temp
FROM sys.sysforeignkeys sysfk
INNER JOIN sys.foreign_keys fk ON sysfk.constid=fk.object_id
INNER JOIN sys.columns c1 ON sysfk.fkeyid=c1.object_id and sysfk.fkey=c1.column_id
INNER JOIN sys.columns c2 ON sysfk.rkeyid=c2.object_id and sysfk.rkey=c2.column_id
order by constid,sysfk.keyno
--building the column list for foreign/primary key tables
;with cte
as
(
select distinct
constraint_name,trusted_status
,fk_table
,substring((select ','+fk_col from #temp where constid=c.constid for xml path('')),2,99999) as fk_col_list
,rk_table
,substring((select ','+rk_col from #temp where constid=c.constid for xml path('')),2,99999) as rk_col_list
,check_status
,delete_cascade,update_cascade
from
#temp c
)
--forming the ADD CONSTRAINT query
select
'ALTER TABLE '+fk_table
+' '+trusted_status
+' ADD CONSTRAINT '+constraint_name
+' FOREIGN KEY('+fk_col_list+') REFERENCES '
+rk_table+'('+rk_col_list+')'
+' '+delete_cascade+update_cascade+';'
+' ALTER TABLE '+fk_table+' '+check_status+' CONSTRAINT '+constraint_name
from cte
--dropping the temp tables
drop table #temp
In the above code,
lines 5-20 : we obtain the base data - constraint columns, table names, index properties and other properties and push them into a #temp table
lines 23-36 : we get the multiple columns to be padded into a single line as a part of the cte block (can be achieved using a subquery/another temp table as well)
lines 38-46 : framing of the exact ADD CONSTRAINT query.
Execution of the above code provides a result as follows, which when executed performs the action of creating the respective foreign keys on the database:
The generated ADD CONSTRAINT code, when formatted would look like below:
ALTER TABLE [dbo].[t2] WITH CHECK ADD CONSTRAINT [fk_t2_t1_id_name] FOREIGN KEY([id],[name])
REFERENCES [dbo].[t1]([id],[name]) ;
ALTER TABLE [dbo].[t2] CHECK CONSTRAINT [fk_t2_t1_id_name]
Note that the above script first creates the constraint (lines 1-2) based on the is_trusted property and then enables/disables it (based on the is_disabled property) using the statement that follows.
Primary Keys
One can obtain the basic information about the primary keys existing in the database from sys.sysconstraints and sys.key_constraints. These tables give us a fair idea of the base table name, the constraint name and the columns these act upon. Though these details form the crux of a primary key constraint, info such as index type being used with the primary key, the order of columns and the current status of the constraint are also equally important.
This information can be obtained using other system and information_schema tables such as:
- information_schema.key_column_usage
- sys.indexes
- sys.index_columns and the like..
So having had the base tables, it only requires to pair them up with the right set of joins using the key columns and correct usage of column data to frame the query.
Drop Primary key
A primary key of a table can be dropped only when there are not any dependent foreign key constraints. The action of dropping foreign keys can be achieved using the script given in the previous section.
It requires just the name of the constraint and the base table name to frame the query for dropping of a primary key. Having obtained them from sys.key_constraints, the query can be designed as follows:
-------------------------------------------------
--ALTER TABLE DROP PRIMARY KEY CONSTRAINT Queries
-------------------------------------------------
SELECT DISTINCT
'ALTER TABLE '+QUOTENAME(object_schema_name(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id))+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sys.key_constraints skc
WHERE type='PK'
This gives us a result as below, which when copied and run in the query window, goes ahead and drops all the primary keys:
Add Primary key
Creation of primary keys as an "ALTER TABLE tbl_name ADD CONSTRAINT constr_name .." syntax requires more details than seen above. With the usage of other system tables we can obtain the necessary details and script the query as follows:
---------------------------------------------------
--ALTER TABLE CREATE PRIMARY KEY CONSTRAINT Queries
---------------------------------------------------
SELECT
QUOTENAME(object_schema_name(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id)) as pk_table--PK table name
,skc.object_id as constid
,QUOTENAME(skc.name) as constraint_name--PK name
,QUOTENAME(iskcu.column_name) + CASE WHEN sic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END as pk_col
,iskcu.ordinal_position
,CASE WHEN unique_index_id=1 THEN 'UNIQUE' ELSE '' END as index_unique_type
,si.name as index_name
,si.type_desc as index_type
,QUOTENAME(fg.name) as filegroup_name
,'WITH('
+' PAD_INDEX = '+CASE WHEN si.is_padded=0 THEN 'OFF' ELSE 'ON' END +','
+' IGNORE_DUP_KEY = '+CASE WHEN si.ignore_dup_key=0 THEN 'OFF' ELSE 'ON' END +','
+' ALLOW_ROW_LOCKS = '+CASE WHEN si.allow_row_locks=0 THEN 'OFF' ELSE 'ON' END +','
+' ALLOW_PAGE_LOCKS = '+CASE WHEN si.allow_page_locks=0 THEN 'OFF' ELSE 'ON' END
+')' as index_property
--,*
into #temp
FROM sys.key_constraints skc
INNER JOIN information_schema.key_column_usage iskcu ON skc.name=iskcu.constraint_name
INNER JOIN sys.indexes si ON si.object_id=skc.parent_object_id and si.is_primary_key=1
INNER JOIN sys.index_columns sic on si.object_id=sic.object_id and si.index_id=sic.index_id
INNER JOIN sys.columns c ON sic.object_id=c.object_id AND sic.column_id=c.column_id
INNER JOIN sys.filegroups fg on si.data_space_id=fg.data_space_id
WHERE
skc.type='PK'
AND iskcu.column_name=c.name
--AND object_name(skc.parent_object_id) in ('t1','t2','t3')
ORDER BY skc.parent_object_id,skc.name,ordinal_position
;with cte
as
(
select
pk_table
,constraint_name
,index_type
,substring((select ','+pk_col from #temp where constid=t.constid for xml path('')),2,99999) as pk_col_list
,index_unique_type
,filegroup_name
,index_property
from #temp t
)
--forming the ADD CONSTRAINT query
select distinct
'ALTER TABLE '+pk_table
+' ADD CONSTRAINT '+constraint_name
+' PRIMARY KEY '+cast(index_type collate database_default as varchar(100))
+' ('+pk_col_list+')'
+index_property
+' ON '+filegroup_name+''
from cte
--dropping the temp tables
drop table #temp
In the above code,
lines 4-32 : we obtain the base data - constraint columns, table names, index properties and other properties and push them into a #temp table
lines 34-46 : we get the multiple columns to be padded into a single line as a part of the cte block (can be achieved using a subquery/another temp table as well)
lines 48-55 : framing of the exact ADD CONSTRAINT query.
Execution of the above code provides a result as follows, which when executed performs the action of creating the respective primary keys on the database:
The generated code when formatted would look like below:
ALTER TABLE [dbo].[#07420643] ADD CONSTRAINT [PK__#07420643__08362A7C] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH( PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
Default Constraints
Default constraints on a column allows for automatic population of data in the absence of user supplied values. These can be created during the table creation itself or by means of an ALTER TABLE statement as well.
Drop Default Constraint
The default constraint existing on a column of a table can be dropped with the knowledge of the table name and the corresponding default constraint name. The following script generates these DROP CONSTRAINT statements using info from sys.default_constraints table.
---------------------------------------------
--ALTER TABLE DROP DEFAULT CONSTRAINT Queries
---------------------------------------------
select
'ALTER TABLE '+QUOTENAME(object_schema_name(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id))
+' DROP CONSTRAINT '+QUOTENAME(sdc.name)+''
from sys.default_constraints sdc
The execution of the above query gives an output as below, which when copied and executed drops the corresponding default constraints:
Add Default Constraint
The ADD CONSTRAINT query can be generated by using the default definition and other columns of the sys.default_constraints system table as follows:
---------------------------------------------
--ALTER TABLE CREATE DEFAULT CONSTRAINT Queries
---------------------------------------------
select
'ALTER TABLE '+QUOTENAME(object_schema_name(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id))
+' ADD CONSTRAINT '+QUOTENAME(sdc.name)+' DEFAULT '+definition+' FOR '+QUOTENAME(c.name)+''
from sys.default_constraints sdc
inner join sys.columns c on sdc.parent_object_id=c.object_id and sdc.parent_column_id=c.column_id
The execution gives an output as below which when executed creates the respective default constraints:
Conclusion
In this article, we have seen the methods and scripts to generate the DROP AND ADD queries for primary keys, foreign keys and default constraints. The scripts generate the queries which are to be executed in the query window for the desired functionality.