0

How can I delete all records from all tables in a database, where the table has a column called systemid where systemid does not equal 1 or 2?

So I need to see if the table contains a certain column name, and if yes, check value of that column for all records, if not 1 or 2, delete. On all tables in the db.

Trying to clean-up a development db.

--- UPDATE ---

I found this SO thread: SQL Server : check if table column exists and remove rows

Which details the following:

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TAB1')
  IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TAB1' AND  COLUMN_NAME = 'COL1')
    delete TAB1 where COL1 not in (select COL2 from TAB2);

but I can't for the life of me correctly from a SQL query that can do what I wan to achieve due to both lack of knowledge and experience. Could anyone please provide a sample code with an explanation?

Thank you overflowers!

Community
  • 1
  • 1
Arbiter
  • 486
  • 1
  • 8
  • 21
  • use `information_schema.columns` .. – Sathish Apr 16 '14 at 13:55
  • as it's mentioned above you can use the information_schema tables/columns. I would create a stored procedure which reads the information_schema and for each row in the select created **delete** statement – StanislavL Apr 16 '14 at 14:00

1 Answers1

0
DECLARE @TableName VARCHAR(128);
DECLARE @MyColumn VARCHAR(128);
SET @MyColumn = 'MyColumnName'
DECLARE MyCursor CURSOR FOR  
(SELECT OBJECT_NAME(c.id) as ObjectName
              FROM dbo.syscolumns c
              WHERE 
                OBJECTPROPERTY(c.id,'ISTABLE') = 1 --Search for tables only
                AND c.name = @MyColumn)

OPEN MyCursor 
FETCH NEXT FROM MyCursor into @TableName
WHILE @@FETCH_STATUS = 0  
BEGIN
    EXEC
            (
                 'DELETE ' + @MyColumn
                +' FROM ' + @TableName  
                +' WHERE ' + @MyColumn + ' not  in (1,2)'
            )
    FETCH NEXT FROM MyCursor into @TableName
END
CLOSE MyCursor   
DEALLOCATE MyCursor
SocratesG
  • 101
  • 1
  • 3