When we are depoying application in We need to write the SQL Scripts in such way that the script can be executed without any issues how many times we run it.
For Example if we want to add a constraint to a column for an existing table we should write simple adding the constraint logic. Because if write like that when we run the same script for the 2nd time we’ll be getting error message saying that “Constarint with the specified name already exists.” To get rid of this kind of issues first we need to check whether the Constraint or Keys already exists in Database. If not exists then only we have to create the required one. Below are the scripts generally used to check for the same.
Script to Check for Non-Clustered & Primary Keys:
select object_name(i.object_id) as objectName, i.name as indexName, sum(p.rows) as rowCnt
from sys.indexes i
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
where i.object_id = object_id('tblTest')
and i.index_id <= 1
group by i.object_id, i.index_id, i.name
To know the number of Rows in table Join with sys.partitions otherwise leave it.
Script to Check Default Constraints:
select t.name, d.name,c.name
from sys.tables t
join sys.default_constraints d on d.parent_object_id = t.[object_id]
join sys.columns c on c.[object_id] = t.[object_id] and c.column_id = d.parent_column_id
where d.name = 'pob_default_current' and t.name = 'em_aduit_place_of_business'
While creating the Keys or Indexes or Constraints it is best practice to give a proper name to it. If you didn’t specify the name SQL will give it a random number. If you want to revert back or drop the changes at that time it will be very difficult as the name is system generated. Here is the sample How to handle the Syster Generated Names.
If Default Constraint Name is System Generated:
IF exists(SELECT 1 FROM sysobjects,syscolumns WHERE sysobjects.id = syscolumns.id AND sysobjects.name = 'am_exam_entities' ANDsyscolumns.name = 'created_dt')
BEGIN
declare @default sysname, @sql nvarchar(max)
SELECT @default = d.name
from sys.tables t
join sys.default_constraints d on d.parent_object_id = t.[object_id]
join sys.columns c on c.[object_id] = t.[object_id] and c.column_id = d.parent_column_id
where t.name = 'am_exam_entities'
and c.name = 'created_dt'
IF @default IS NOT NULL
BEGIN
set @sql = N'alter table am_exam_entities drop constraint ' + @default
exec sp_executesql @sql
END
ALTER TABLE am_exam_entities DROP COLUMN created_dt
END
Go
Hope you enjoyed this article. Will catch you next time with some other interesting article.
