Delete row if table exists SQL

Question

I have a script that drops a load of tables using DROP TABLE IF EXISTS, this works.

There is also a delete in this script to DELETE a row from another table that I do not manage. This table may or may not exist.Is there any to check the table exists before attempting to delete a row?

this needs to work for MYSQL and SQLServer

thanks Alex

`DROP TABLE IF EXISTS` doesn't work in SQL Server anyway. You will need 2 separate scripts. — Martin Smith, Mar 21 '12 at 11:28

score 14 · Accepted Answer · answered Mar 21 '12 at 11:31

To check in SQL SERVER,

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

To check in mysql:

You simply count:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

score 7 · Answer 2 · edited Apr 13 '17 at 12:42

7

This one deletes the row and does not complain if it can't.

DELETE IGNORE FROM table WHERE id=1

source here.

edited Apr 13 '17 at 12:42

Community

1
1

answered Sep 11 '12 at 12:09

3

At least for MySQL this does not actually work at all on a table that doesn't exist, as commented on the very link provided. – jlh Nov 23 '18 at 09:30

score 3 · Answer 3 · answered Mar 21 '12 at 11:29

3

For SQL Server: You could use:

IF OBJECT_ID('tablename','U') IS NOT NULL

answered Mar 21 '12 at 11:29

Darren

68,902
24
138
144

do you mean this: `IF OBJECT_ID('[MyTable]', 'U') IS NOT NULL delete from [MyTable];` – Przemyslaw Remin Dec 31 '18 at 15:32

score 3 · Answer 4 · answered Mar 21 '12 at 11:30

I dont think you'll find a common syntax between SQL server and my SQL. I mean, you can check if the table exsits on SQL Server using something like:

if exists(select * from sys.objects where name like 'table_name')

but mySql would have its own catalog.

Unless you write a script like:

if (sql_server) then
   if exists(select * from sys.objects where name like 'table_name')
else --mySQl
   --execute the mysql script

score 2 · Answer 5 · answered Mar 21 '12 at 11:29

2

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLE_NAME]') AND type in (N'U'))

answered Mar 21 '12 at 11:29

Chris Gessler

22,727
7
57
83

score 1 · Answer 6 · edited May 23 '17 at 10:30

1

It seems to me right the first item in the "Related" column on the right side answers your question.... Check if table exists in SQL Server

edited May 23 '17 at 10:30

Community

1
1

answered Mar 21 '12 at 11:28

MrTJ

13,064
4
41
63

score 1 · Answer 7 · answered Mar 21 '12 at 11:30

1

For MySQL

show tables like "test1";

For SQL Server

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testSchema' AND     TABLE_NAME = 'test1'

answered Mar 21 '12 at 11:30

Chetter Hummin

6,687
8
32
44

score 1 · Answer 8 · answered Mar 21 '12 at 11:31

A question you want to ask yourself (in terms of database design): Why are you trying to delete rows from a table you are not sure exists? If it doesn't, but you expect it does, wouldn't you rather create the table than not delete it?

Anyway, Chris Gesslers answer does exactly what you are asking in SQL Server, but there is some smell here.

The construct in MySQL you can use is

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename'

and check for results

score 0 · Answer 9 · answered Sep 15 '19 at 06:24

0

you can use bellow code:

DECLARE @TABLENAME VARCHAR(20)='TableName';

IF (OBJECT_ID(@TABLENAME) IS NOT NULL )
BEGIN
   execute(N'TRUNCATE TABLE ' + @TABLENAME + '' );
END
ELSE
BEGIN 
  PRINT 'Table NOT Exists'
 END

answered Sep 15 '19 at 06:24

Reza Jenabi

3,884
1
29
34

Delete row if table exists SQL

9 Answers9

Linked