{"id":98,"date":"2010-01-17T17:09:54","date_gmt":"2010-01-17T22:09:54","guid":{"rendered":"http:\/\/wukenny.host-ed.net\/?p=98"},"modified":"2010-01-17T17:09:54","modified_gmt":"2010-01-17T22:09:54","slug":"check-if-a-table-exists-in-sql-server","status":"publish","type":"post","link":"http:\/\/kennywu.info\/?p=98","title":{"rendered":"Check if a table exists in SQL Server"},"content":{"rendered":"<p>It is always a good idea to delete a table, especially for temporary tables, before you start creating one. The question is how do you determine if a table exists?<br \/>\nTwo methods will be examined, INFORMATION_SCHEMA and OBJECT_ID.<br \/>\n<strong>INFORMATION_SCHEMA<\/strong><br \/>\nThis is the easiest and most forward compatible way to get what you want. It is actually a view based on existing stored procedures\/functions.The schema describes the structure of the database, including tables, view, etc. It will tell you for example, a list of tables, a list of the parameters of a particular stored procedure, a list of all user-defined functions, and so on.<br \/>\n<code>IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE SCHEMA_NAME = 'MySchema' AND  TABLE_NAME = 'MyTable'))<br \/>\nBEGIN<br \/>\n        --Do Stuff<br \/>\nEND<\/code><br \/>\nTo get a list of stored procedures\/functions.<br \/>\n<code>SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES  -- 'FUNCTIONS' <\/code><br \/>\nFor curious mind wants to know how INFORMATION_SCHEMA really works, here is the way to see how Microsoft implemented it<br \/>\n<code>execute sp_helptext \"information_schema.tables\"<br \/>\n-- Identifies tables accessible to the current user<br \/>\nCREATE VIEW INFORMATION_SCHEMA.TABLES<br \/>\nAS<br \/>\nSELECT<br \/>\n db_name()   AS TABLE_CATALOG,<br \/>\n s.name    AS TABLE_SCHEMA,<br \/>\n o.name    AS TABLE_NAME,<br \/>\n CASE o.type<br \/>\n  WHEN 'U' THEN 'BASE TABLE'<br \/>\n  WHEN 'V' THEN 'VIEW'<br \/>\n END    AS TABLE_TYPE<br \/>\nFROM<br \/>\n sys.objects o LEFT JOIN sys.schemas s<br \/>\n ON s.schema_id = o.schema_id<br \/>\nWHERE<br \/>\n o.type IN ('U', 'V')<br \/>\n<\/code><br \/>\n<strong>OBJECT_ID <\/strong><br \/>\nIt actually is a system stored procedure. An issue of being a sys sproc is that it tend to change version by version, in terms of input parameters and return value(s). It will tell you if the object exists but not guarantee it is a table type. there is the syntax.<br \/>\n<code>IF OBJECT_ID('*objectName*') IS NOT NULL<\/code><br \/>\nWhen a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: <code>SELECT OBJECT_ID('tempdb..#mytemptable').<\/code><br \/>\n<strong>Conclusion:<\/strong><br \/>\nUse INFORMATION_SCHEMA whenever possible and only use systems stored procedure like OBJECT_ID, when the info you want is not in the INFORMATION_SCHEMA.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It is always a good idea to delete a table, especially for temporary tables, before you start creating one. The question is how do you determine if a table exists? Two methods will be examined, INFORMATION_SCHEMA and OBJECT_ID. INFORMATION_SCHEMA This &hellip; <a href=\"http:\/\/kennywu.info\/?p=98\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[33,44],"class_list":["post-98","post","type-post","status-publish","format-standard","hentry","category-database","tag-information_schema","tag-object_id"],"_links":{"self":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/posts\/98","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=98"}],"version-history":[{"count":0,"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/posts\/98\/revisions"}],"wp:attachment":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=98"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=98"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=98"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}