Checking for and dropping an existing table via C# and SMO

Question

I am trying to look for a SQL table by name and if it exists drop it. This all needs to be done in C# using SMO.

To complicate it a bit more the table also has a schema other then "dbo".

Ultimatly the table will be recreated via SMO (I have this working) but I have to make sure it is not there before I can recreate it.

All of the examples that I have seen seem to be creating and then dropping the table all in the same context. In my case the table will have been created and populated in a previous session.

score 12 · Answer 1 · answered Aug 15 '11 at 18:43

12

var connection = new SqlConnection(connectionString);
var server = new Server(new ServerConnection(connection));

db = server.Databases["YourFavDB"];

db.Tables["YourHatedTable"].Drop();

answered Aug 15 '11 at 18:43

Kevin Driedger

51,492
15
48
55

The only one to answer the question and not a single upvote until now. Is there any justice on SO? – MrTelly Jun 12 '12 at 11:27
1

There's no mention of schema here. It could be either completely reasonable, or dangerous to assume the schema is dbo, depending on your environment – Davos Jan 18 '16 at 01:02
Good answer, I think DropIfExists() would be better – DazedandConfused Mar 08 '17 at 08:51

score 4 · Answer 2 · answered Aug 12 '14 at 20:31

4

I think the best approach would be:

Microsoft.SqlServer.Management.Smo.Database myDataBase = myServer.Databases["myDataBaseName"];
bool tableExists= myDataBase.Tables.Contains("myTable");
if (tableExists)
{
   myDataBase.Tables["myTable"].Drop();
}

answered Aug 12 '14 at 20:31

This should absolutely be the accepted answer to this question. – Dan Forbes Apr 19 '16 at 20:06

score 1 · Answer 3 · answered May 29 '09 at 22:14

1

Couldn't you just wrap your DROP TABLE statement in a try/catch block, and discard any errors that occur?

Anyway, the sql to determine if a table exists is:

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

answered May 29 '09 at 22:14

Robert Harvey

178,213
47
333
501

3

It goes against my grain to use errors for program flow control. Thanks for the response. – John S Jun 02 '09 at 15:08
1

Well, couldn't you use the IF EXISTS I provided to check first, if you don't like the Try/Catch? – Robert Harvey Jun 02 '09 at 15:58

score 0 · Accepted Answer · answered May 29 '09 at 22:11

0

First question is, why can you not drop and recreate with DDL?

And in answer to your question:

Table table = new Table(myDatabase, "MyTable", "MySchema");

answered May 29 '09 at 22:11

jrista

32,447
15
90
130

Thank you for your answer. I have actually gone down the same route but different path: myTable.Schema = "MySchema" The table structure itself is determined on the fly by parsing an XML schema. – John S Jun 02 '09 at 15:06

Checking for and dropping an existing table via C# and SMO

4 Answers4

Linked