0

I was looking this article

and I have just a question how to add IFEXISTS() in the select part: I want to run @command only if there some rows. I think it will throw an error if I dont check select part?

declare @table_name nvarchar(256)

declare @Command nvarchar(max) = ''

set @table_name = N'ATableName'

select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
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 c.name in ('column1','column2','column3')

--print @Command

execute (@Command)
Stefan0309
  • 1,602
  • 5
  • 23
  • 61
  • 1
    Name your constraints. I don't know if a DBA will like the idea of running a query like this one (could be dangerous) in the prod environment. – The Impaler Jul 18 '22 at 13:12
  • Yes, but my job here is to find some columns that has automated generated constraint names, and to do a drop of such constraints. @TheImpaler do you have some better approach? This article that I've just linked is old several years.. – Stefan0309 Jul 18 '22 at 13:21
  • 1
    If you don't know the name of the constraint(s) this is the way of doing it. However, I would strongly suggest you decouple the last part `execute (@Command)` into a separate step. Use the first part of the code to generate a "drop-constraint.sql" SQL script. Then inspect this SQL script to make sure the code is correct (you may find some surprises there) and clean it. Once you are sure the code is correct, run the script. – The Impaler Jul 18 '22 at 13:31

0 Answers0