CODEWITHSUNDEEP
c#sql-server
Partha
Partha

Reputation: 2192

How to check if there is a table in sql server or not using c# code?

I like to write c# code through which i like to check whether there is a table or not in sqlserver?

can any one give me a sample code for that?

Upvotes: 1

Views: 1082

Answers (5)

Fredrik Mörk
Fredrik Mörk

Reputation: 158289

This query should give you the answer:

select count(id) from sysobjects where name = 'thetable' and type = 'U'

If count is 1 the table exists, if it is 0 it does not.

Wrapped into a method:

private bool TableExists(string tableName)
{
    using (SqlConnection conn = new SqlConnection(GetConnectionString()))
    {
        using (SqlCommand cmd = new SqlCommand("select count(id) from sysobjects where name = @tableName and type = 'U'", conn))
        {
            cmd.Parameters.AddWithValue("@tableName", tableName);
            conn.Open();
            int count = (int)cmd.ExecuteScalar();
            conn.Close();
            return count == 1;
        }
    }
}

Upvotes: 6

S M Kamran
S M Kamran

Reputation: 4503

What you need is to query sysobject table in your SQLServer database to find the existence of a particular table/object in your database.

SELECT 1 AS Exists FROM dbo.sysobject where name = @tableName AND xtype = 'U'

open up a SQLConnection and wrap this query in SqlCommand object and execute it.

Upvotes: 0

Conceptdev
Conceptdev

Reputation: 3261

For newer SQL Server versions that support it (at least 2005 and 2008) you can write INFORMATION_SCHEMA queries. Eg. the following query (when run against your specific application database and not master) will return a row if there is a Users table.

SELECT * FROM information_schema.tables
WHERE TABLE_NAME = 'Users'
AND   TABLE_TYPE = 'BASE TABLE' -- could be 'VIEW'

or just return all the table names in your database like this:

SELECT TABLE_NAME FROM information_schema.tables
WHERE  TABLE_TYPE = 'BASE TABLE' -- could be 'VIEW'

I'm sure you've already got C# ADO code to run a query (or you could convert the above into a stored procedure). There's heaps of other useful information you can gather without worrying about all the arcane sysobjects parameters/columns/types.

Upvotes: 1

Umair Ahmed
Umair Ahmed

Reputation: 11694

use information_schema

select a.table_name from information_schema.tables a where table_name like ''

Upvotes: 0

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50712

using(SqlConnection connection =
    new SqlConnection(connectionString))
{
connection.Open();
DataTable dt = connection.GetSchema();
connection.Close();
}

see here

Upvotes: 3

Related Questions