how to check existance of a table in sql server?

[Dan]

Hello,

we have an intranet application using Windows Integrated Authentificatio n.
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.ServerV ariables("remot e_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]]

"Dan" <[e-mail] in message
news:O2******** ******@TK2MSFTN GP03.phx.gbl...

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...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mr. Arnold]

"Dan" <[e-mail] in message
news:O2******** ******@TK2MSFTN GP03.phx.gbl...

Hello,

we have an intranet application using Windows Integrated Authentificatio n.
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.ServerV ariables("remot e_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.

[Dan]

Thanks to you two

"Mr. Arnold" <MR. Ar****@Arnold.c omschreef in bericht
news:ON******** ********@TK2MSF TNGP03.phx.gbl. ..

>
"Dan" <[e-mail] in message
news:O2******** ******@TK2MSFTN GP03.phx.gbl...

>Hello,

we have an intranet application using Windows Integrated
Authentificati on. 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.Server Variables("remo te_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.

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

If exists(Tablenam e) 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" wrote:

>
"Dan" <[e-mail] in message
news:O2******** ******@TK2MSFTN GP03.phx.gbl...

Hello,

we have an intranet application using Windows Integrated Authentificatio n.
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.ServerV ariables("remot e_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.

[Mr. Arnold]

"Peter Bromberg [C# MVP]" <pb*******@yaho o.yabbadabbadoo .comwrote in
message news:36******** *************** ***********@mic rosoft.com...

If exists(Tablenam e) 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_SCH EMA views.

\\\
select *
from INFORMATION_SCH EMA.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_SCH EMA 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,

--

(O)enone

[Mark Rae [MVP]]

"(O)enone" <oe****@nowhere .comwrote in message
news:H_******** *********@newsf e1-gui.ntli.net...

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
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[\(O\)enone]

Mark Rae [MVP] wrote:

>won't break on future versions of SQL Server

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_SCH EMA 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.

--

(O)enone

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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" wrote:

>
"Peter Bromberg [C# MVP]" <pb*******@yaho o.yabbadabbadoo .comwrote in
message news:36******** *************** ***********@mic rosoft.com...

If exists(Tablenam e) 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.