objConnection.Open _\n “Provider=SQLOLEDB;Data Source=atl-sql-01;” & _\n “Trusted_Connection=Yes;Initial Catalog=Fabrikam”\n<\/PRE>\nAnd yes, as a matter of fact it is<\/I> easy to modify this script for your own use. Just replace atl-sql-01<\/B> with the name of the SQL Server machine you want to connect to, then replace Fabrikam<\/B> with the name of the database you want to check. (Don\u2019t be confused by the label Initial Catalog<\/I>; we\u2019re really just talking about a database here.)<\/P>\n
What do you know: Nick Swisher just led off the top of the ninth with a double. <\/P>\n
Uh, or so we heard someone mention as they walked by. Too bad those people aren\u2019t busy working like the rest of us.<\/P>\n
After we\u2019ve made the connection to the SQL Server computer we then issue the following command in order to return a recordset consisting of all the tables in the Fabrikam database that have the name EmployeeData:<\/P>
objRecordSet.Open “SELECT * FROM sysobjects Where Name= ‘EmployeeData’ AND xType= ‘U'”, _\n objConnection, adOpenStatic, adLockOptimistic\n<\/PRE>\nWhat do you mean you don\u2019t see how we did that? Why it\u2019s as plain as the nose on your \u2013 oh, maybe it\u2019s not so plain after all, is it? Well, let\u2019s see what we can do to help clear things up. To begin with, all SQL Server databases have a table named sysobjects. Sysobjects has one function: it keeps track of all the objects (tables, logs, constraints, stored procedures, etc.) that have been created within the database. As you can probably guess, that\u2019s why we query the sysobjects table: if anyone knows whether or not a table exists sysobjects does. We then include the Where clause Where Name = ‘EmployeeData’<\/B> for obvious reasons: that\u2019s the name of the table we\u2019re interested in. <\/P>\n
That leaves us with this additional clause:<\/P>
AND xType= ‘U’\n<\/PRE>\nAs it turns out, sysobjects includes a field named xType<\/B>. This field is used to keep track of the object type; a user table has an xType of U<\/B> (system tables have an xType of S<\/B>). This AND clause leaves us with a query that says, \u201cBring me back a collection of all the objects in the database that not only have the Name EmployeeData but also (AND) happen to be a user table.\u201d<\/P>\n
To determine whether or not the database really does<\/I> contain a user table named EmployeeData all we have to do is take a look at the recordset\u2019s RecordCount<\/B> property, a property which \u2013 logically enough \u2013 tells us how many records are in the recordset. RecordCount can be only one of two values: 0, meaning no such tables exist; or 1, meaning that the EmployeeData table does<\/I> exist. (The RecordCount can never be greater than 1 simply because table names must be unique. That means you can only have, at most, one table named EmployeeData.) In this block of code we examine the value of the RecordCount property and then echo back the appropriate message:<\/P>If objRecordset.RecordCount = 0 Then\n Wscript.Echo “The table is not in the database.”\nElse\n Wscript.Echo “The table is in the database.” \nEnd If\n<\/PRE>\nWhoa, 5-2 Oakland, bottom of the ninth. That is, for those of you interested in that sort of thing.<\/P>\n
(Editor\u2019s Note: Update, <\/I>Oakland<\/I> wins <\/I>5-2. Go Dodgers.)<\/I><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I tell whether a specified table exists in a SQL Server database?— LL Hey, LL. Well, as it turns out it\u2019s absolutely impossible to tell whether a specified table exists in a SQL Server database. So, sorry, time to go back to watching the Major League Baseball playoffs. By the […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,3,176,5],"class_list":["post-66323","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-scripting-guy","tag-sql-server","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I tell whether a specified table exists in a SQL Server database?— LL Hey, LL. Well, as it turns out it\u2019s absolutely impossible to tell whether a specified table exists in a SQL Server database. So, sorry, time to go back to watching the Major League Baseball playoffs. By the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66323"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66323\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n