How to find and dynamically delete the default constraint in MS SQL Server

 When we have a condition to delete the constraint where the name of constraint is generated by the database as default then

declare @schema_name Varchar(100)

declare @table_name Varchar(100)

declare @col_name Varchar(100)

declare @Command  varchar(500)

set @schema_name = 'Scott'

set @table_name = 'Employee'

set @col_name = 'IsAdmin'

 

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] 

                                 DROP CONSTRAINT ' + 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 = @table_name

and t.schema_id = schema_id(@schema_name)

and c.name = @col_name

print @Command

execute (@Command)

When we execute this block it will find the constraint details of that column and drop the default constraint.

What other ideas can you add to this post that I may have not mentioned?

How to remove everything after the second decimal(or any special character) and count the occurrence of any character in the string in MS SQL Server DB.

 1.       Remove everything after the second decimal(or any special character)

Suppose we have a string in which we have multiple decimals and we have a requirement to cut the string at the occurrence of the second decimal then.

DECLARE @startParam varchar(50)

Set @startParam ='1111.000.00.00.00'

select left(@startParam, charindex('.', @startParam, charindex('.', @startParam)+1)-1);

O/P:-  1111.000

Here, we have declared one variable as “@StartParam” with the length of 50 characters and passed the value with multiple decimals in the string, so according to the condition, we removed everything after the second decimal in the select statement.

2.       Count the occurrence of any character

When we have same string as given above and as per condition needs to count the decimals(dot) in the string then.

Select (len(@startParam) - len(replace(@startParam,'.','')))

O/P:- 4

What other ideas can you add to this post that I may have not mentioned?