how to check existance of a table in sql server?

[Dan]

Hello,

we have an intranet application using Windows Integrated Authentification.
When an user starts the application, he gets a form for inputting data. The
first time he does that, the application creates in a specific database a
table with the name of his account (read with
Request.ServerVariables("remote_user") and creates in that table the records
he enters. From the second time the user starts the application, still the
same form appears but the table may not be recreated.
How can i check in code-behind (VB) whether that table (e.g. table 'dan'
exists)?

Thanks
Dan

 

[Mark Rae [MVP]]

How can i check in code-behind (VB) whether that table (e.g. table 'dan'
exists)?

Since you don't mention what back-end RDBMS you're using, I'll assume it's
SQL Server...

Whatever method you're using to connect to the RDBMS, use the ADO.NET
ExecuteScaler method on the following SQL:

SELECT COUNT(*) FROM sys.tables WHERE [name] = 'dan' AND [type] = 'U'

If ExecuteScalar returns 1, the table exists - if it returns 0, it
doesn't...

There must be at least half a dozen other ways of doing this...

 

[Mr. Arnold]

Hello,

we have an intranet application using Windows Integrated Authentification.
When an user starts the application, he gets a form for inputting data.
The first time he does that, the application creates in a specific
database a table with the name of his account (read with
Request.ServerVariables("remote_user") and creates in that table the
records he enters. From the second time the user starts the application,
still the same form appears but the table may not be recreated.
How can i check in code-behind (VB) whether that table (e.g. table 'dan'
exists)?

You make a stored procedure and ask the question.

If exist(tablename)

The stored procedure returns a Return code of zero if it's there or non-zero
if it's not there, which you'll check in code the return code that you have
set and returned, taking take the appropriate action.

Use Google where you can ask *How to check if a SQL Table Exist* or
something along those lines. Also look up *How to get a output parm or
return code from a Stored Procedure using ADO.NET* or something along those
lines.

 

[Mark Rae [MVP]]

Since you don't mention what back-end RDBMS you're using, I'll assume it's
SQL Server...

D'oh - apologies - I didn't read the title closely enough...!

 

[Dan]

Thanks to you two

You make a stored procedure and ask the question.

If exist(tablename)

The stored procedure returns a Return code of zero if it's there or
non-zero if it's not there, which you'll check in code the return code
that you have set and returned, taking take the appropriate action.

Use Google where you can ask *How to check if a SQL Table Exist* or
something along those lines. Also look up *How to get a output parm or
return code from a Stored Procedure using ADO.NET* or something along
those lines.

 

[Guest]

If exists(Tablename) does not work in this case. That has to be a legitimate
query, not a "sysobject". Mark's solution would be the preferred one to me.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com

 

[Mr. Arnold]

If exists(Tablename) does not work in this case. That has to be a
legitimate
query, not a "sysobject". Mark's solution would be the preferred one to
me.

What? Do you think I am going to rattle this stuff off the top of my head?
The OP has got the point, and I am sure the OP will find it, the solution,
after being given a little push. That's all it was and nothing else. I am
not going to worry about something as trivial as this.

 

[\(O\)enone]

Mark Rae [MVP] wrote:
[...]

SELECT COUNT(*) FROM sys.tables WHERE [name] = 'dan' AND [type] = 'U' [...]
There must be at least half a dozen other ways of doing this...

A better one IMO is to use the INFORMATION_SCHEMA views.

\\\
select *
from INFORMATION_SCHEMA.Tables
where TABLE_NAME = 'dan'
///

This is an ANSI standard (http://en.wikipedia.org/wiki/Information_Schema).
No directly accessing system tables, no "magic" codes (why does 'type' need
to be set to 'U'?), won't break on future versions of SQL Server and also
works on other RDBMSs.

There are lots of other INFORMATION_SCHEMA views that give access to
columns, views, constraints, stored procedures, etc. To see them all, take a
look at the views that are defined against the master database on your
server.

HTH,

 

[Mark Rae [MVP]]

This is an ANSI standard
(http://en.wikipedia.org/wiki/Information_Schema).
Indeed.

won't break on future versions of SQL Server

You can't know that for sure...

and also works on other RDBMSs.

Apart from the ones which don't support it, e.g. Oracle, Jet etc:
http://www.databasejournal.com/news/article.php/3686366
http://www.thescripts.com/forum/thread199615.html

 

[\(O\)enone]

You can't know that for sure...

Maybe not, but I think it's a much safer bet than selecting from system
tables. If MS decide to reorganise the internals of SQL Server (and I'm sure
it's a possibility!), I would certainly hope they would ensure that the
INFORMATION_SCHEMA views keep working. And if not, it would be much easier
to modify they to maintain their previous functionality than to fix every
bit of code that selected from the system tables directly.

 

[Guest]

You know, people make mistakes. I certainly do. But acting like you're from
another planet doesn't add much clarity to the thread, IMHO.
Cheers.
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com

 

[Mr. Arnold]

You know, people make mistakes. I certainly do. But acting like you're
from
another planet doesn't add much clarity to the thread, IMHO.

Don't let that MVP go to your head now. It was just a simple example of how
to do something, and it was not a federal case that needed to be made by
you, with you giving your opinion on something.

 

[Mark Rae [MVP]]

You know, people make mistakes. I certainly do. But acting like you're
from
another planet doesn't add much clarity to the thread, IMHO.

Haven't you killfiled this irritating cretin yet...?

 

[DArnold]

Haven't you killfiled this irritating cretin yet...?

He can do me a favor and do it, because I don't particularly want to
hear from him about anything.

I thought I made that clear over there in the C# NG, and that applies
to a couple of you MVP(s) that I don't want to hear from you. I can't
make it any plainer than that. I am not seeking any of you out to
communicate with any of you, period and please do the same with me.

I am not out here trying to make friends with any one out here on the
Internet, so do me a favor and stay out of my face.

 

[Guest]

My Grandmother, who lived to be 100, had a more descriptive word for people
like this. It is "Schmuck".
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com

 

[Patrice]

Also as a side note my personal default preference would be to create a
single table and have one row for each user...