Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Does table exist

Hi,

I am posting this in this section as I need to know how to respond to the condition, in ASP.NET

Basically all I want to do is , if a table exists in the Sql Database, then write "EXISTS!" to a span I have on the page

Thanks in advance

Andrew
0
REA_ANDREW
Asked:
REA_ANDREW
  • 7
  • 6
  • 3
  • +1
2 Solutions
 
aprestoCommented:
Hi REA_ANDREW,

assuming your using SQL Server you can use this query to check if the table exists:

SELECT [Name] AS [Table Name]
FROM sysObjects
WHERE xType = 'U' AND [Name] = 'YourTableName'
ORDER BY [Name]

 and just check if something is returned

Apresto
0
 
TimCotteeCommented:
Hi REA_ANDREW,

Select Case When (Select Count(*) from dbo.sysobjects where name ='Users' and xtype = 'U') = 1 Then 'Exists' Else 'Doesn''t Exist' End As IstItThere

For example, tests for the existence of a table called Users.

Tim Cottee
0
 
REA_ANDREWAuthor Commented:
hi Apresto, thanks you are a reliable help.  

Can you confirm that I could use an ExecuteScalar() with this SQL String?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aprestoCommented:
in c# sometinhg like this:

string tableName = "YourTable";
string sql = "SELECT count(*) as TableCount FROM sysObjects  WHERE xType = 'U' [Name] = " + tableName + "ORDER BY [Name]";

SqlCommand command = new SqlCommand( sql );
command.Connection = YourConnection;
...etc etc...

int tableCount = command.ExecuteScalar();

if ( tableCount > 0 )
{
   //EXISTS
}
0
 
REA_ANDREWAuthor Commented:
Aha I post to slow, thanks also TimCottee
0
 
aprestoCommented:
hi there, well spotted, if you are just trying to see if it exists you could just return a count and check its not 0 (obviously it wont ever be more than one either :o)
0
 
aprestoCommented:
apologies, didnt spot your post Tim Cottee - didnt mean to duplicate
0
 
REA_ANDREWAuthor Commented:
So how do I implement, the correct one in C#, i.e. is this the one to use?

Select Case When (Select Count(*) from dbo.sysobjects where name ='Users' and xtype = 'U') = 1 Then 'Exists' Else 'Doesn''t Exist' End As IstItThere

Andrew
0
 
TimCotteeCommented:
apresto,

No problem.

Tim
0
 
TimCotteeCommented:
REA_ANDREW,

Either really, all mine does is wrap the "Exists" into the sql statement so you can executescalar on it straight to your span. apresto showed how to use the version that simply returns the count to do the same thing.

Tim
0
 
REA_ANDREWAuthor Commented:
Thanks guys, Big Help
0
 
aprestoCommented:
Glad to have helped

Good Luck

Apresto
0
 
REA_ANDREWAuthor Commented:
Just one more quick one, is this a correct implemntation as I seem to remeber that a null reference is thrown in the execute scalar if nout is in there,  I like using the one line implementation, not sure of the name, but could either of you please confirm this is correct

int tableCount = command.ExecuteScalar() = null ? 0 : command.ExecuteScalar();

Thanks

Andrew
0
 
SKumar_1981Commented:
Try this
Declare Isexist int
SELECT COUNT(*) [Name] AS [Table Name
FROM  information_schema.tables
WHERE xType = 'U' AND [Name] = 'YourTableName'
ORDER BY [Name])

if @Isexist >0
begin
      SET @Exists = 1
end
else
      SET @Exists=0

Regards,
skumar


Regards,
skumar
0
 
aprestoCommented:
because you are using a count() you will always get a value, a 0 if nothing is found
0
 
REA_ANDREWAuthor Commented:
Excellent thanks
0
 
aprestoCommented:
No Problemo :o)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now