Hi everyone. I am struggling to write this cursor below, basically I need to delete records for multiples tables that employee = X22. I will get all the table names from this condition: FROM SYS.M_TABLES WHERE SCHEMA_NAME ='X' AND TABLE_NAME LIKE '%DATA%'
BEGIN
DECLARE @CMD VARCHAR(4000);
DECLARE CMDS CURSOR FOR
SELECT 'DELETE FROM [' + TABLE_NAME + '] WHERE EMPLOYEE = ''X22'''
FROM SYS.M_TABLES
WHERE SCHEMA_NAME ='X' AND TABLE_NAME LIKE '%DATA%'
OPEN CMDS
FETCH NEXT From CMDS INTO @CMD
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE(@CMD)
FETCH NEXT From CMDS INTO @CMD
END
CLOSE CMDS
DEALLOCATE CMDS
END
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.