CODEWITHSUNDEEP
sqlsql-servert-sql
ErocM
ErocM

Reputation: 4662

Determine if column exists in table and run code if so

I'm trying to loop through my tables and if a column name exists then delete all records within that table.

Here is what I have so far:

  select *
  from information_schema.columns
  order by table_name, ordinal_position

  IF EXISTS(
    select 'CorporationId'
    from information_schema.columns
    where TABLE_NAME = @tn
  )
  BEGIN
    PRINT 'Stored procedure already exists';
    declare @sql nvarchar(max) = concat('DELETE FROM ', @tn, ' WHERE CorporationId = @CorporationId');
    print @sql;
    exec sp_executesql @sql, N'@CorporationId uniqueidentifier', @CorporationId=@CorporationId;
  END;

At this point, I'm just getting a bunch of CorporationId and I'm not returning that it exists. How do I determine if the column exists and run code if it does?

Upvotes: 1

Views: 615

Answers (1)

گلی
گلی

Reputation: 240

You ca use below code:

IF EXISTS(
    select *
    from information_schema.columns
    where TABLE_NAME = @tn AND COLUMN_NAME = N'CorporationId'
  )
  BEGIN
    PRINT 'Stored procedure already exists';
    declare @sql nvarchar(max) = concat('DELETE FROM ', @tn, ' WHERE CorporationId = @CorporationId');
    print @sql;
    exec sp_executesql @sql, N'@CorporationId uniqueidentifier', @CorporationId=@CorporationId;
  END;

Upvotes: 3

Related Questions