Check SQL table exist or not in C#

Question

0.00/5 (No votes)

See more:

I want to check if the SQL table exists or not. If not exists, I will run create if exists, I will select some fields from that table.

What I have tried:

I wrote this for checking. But some error is coming that "Syantax error near tempoutlet"
below my code

bool exists;
            var chktb = new SqlCommand("select case when exists(select table_schema,table_name from information_schema.tables where schema_name='inventoryDB.mdf' and table_name='dbo.tempoutlet')", con);
            exists = (int)chktb.ExecuteScalar() == 1;

So any solution please

Posted 9-Jun-17 18:43pm

vijay_bale

Updated 7-Dec-19 23:12pm

v3

Add a Solution

3 solutions

Solution 3

SELECT CASE WHEN OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL THEN 1 ELSE 0 END.
what does 'U' refers to?

Posted 7-Dec-19 20:58pm

Member 14682056

Comments

CHill60 9-Dec-19 4:28am

If you want to comment on, or ask a question about, a post then use the "Have a Question or Comment?" link next to it.
In this instance looking at the documentation is the better route OBJECT_ID (Transact-SQL) - SQL Server | Microsoft Docs[^] - it means that we are looking for the object id of a (User defined) table.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-06-09T19:42:00

Solution 1

Try this:

SQL

SELECT CASE WHEN OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL THEN 1 ELSE 0 END

Posted 9-Jun-17 19:42pm

OriginalGriff

Comments

vijay_bale 10-Jun-17 3:42am

Your solution worked.

Member 14682056 8-Dec-19 2:59am

SELECT CASE WHEN OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL THEN 1 ELSE 0 END.
What does 'U' refers to?

OriginalGriff 8-Dec-19 6:00am

https://docs.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql?view=sql-server-ver15

bagadurshah limbo 13-Dec-21 0:58am

string tableCheck = "SELECT CASE WHEN OBJECT_ID('[MyAppDB].[User]', 'U') IS NOT NULL THEN 1 ELSE 0 END"; is this the correct format??

kmoorevs · Accepted Answer · 2017-06-10T11:30:00

I know you already have a working solution, but just wanted to offer another. An alternate method would be to simply execute your select command in a try/catch. If the table doesn't exist, you can use the catch block to create the table. This avoids the overhead of a schema lookup every time.