 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / July 2007how to check existance of a table in sql server?
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 > 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...  SignatureMark Rae ASP.NET MVP http://www.markrae.net > 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...! SignatureMark Rae ASP.NET MVP http://www.markrae.net [...] > 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, Signature(O)enone > 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 SignatureMark Rae ASP.NET MVP http://www.markrae.net >> 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_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. Signature(O)enone > Hello, > [quoted text clipped - 7 lines] > 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. Thanks to you two >> Hello, >> [quoted text clipped - 20 lines] > return code from a Stored Procedure using ADO.NET* or something along > those lines. 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 > > Hello, > > [quoted text clipped - 20 lines] > return code from a Stored Procedure using ADO.NET* or something along those > lines. > 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. 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. SignatureSite: http://www.eggheadcafe.com UnBlog: http://petesbloggerama.blogspot.com BlogMetaFinder(BETA): http://www.blogmetafinder.com > > If exists(Tablename) does not work in this case. That has to be a > > legitimate [quoted text clipped - 5 lines] > 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. > 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. > 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...? SignatureMark Rae ASP.NET MVP http://www.markrae.net >> 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...? 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. My Grandmother, who lived to be 100, had a more descriptive word for people like this. It is "Schmuck". SignatureSite: http://www.eggheadcafe.com UnBlog: http://petesbloggerama.blogspot.com BlogMetaFinder(BETA): http://www.blogmetafinder.com > > 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...? Also as a side note my personal default preference would be to create a single table and have one row for each user... -- Patrice > Hello, > [quoted text clipped - 10 lines] > Thanks > Dan Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.