Search
Monday, November 19, 2018 ..:: Home ::.. Register  Login
   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

Does that table or stored proc already exist? How to check before you create it

Feb 22

Written by:
Saturday, February 22, 2014 4:45 PM  RssIcon

TSqlFundamentals You’re creating a proc, but getting errors on creation, cause it already exists…what SHOULD you have done and what to do now?

 

You’re creating a proc to do something, and you need to create another proc, or a table, or something.  You create it, CTRL+R in Management Studio (or CTRL+SHIFT+E in VS), you get an error, make some changes, CTRL+R again but now you’re getting a different error.  HHHMMM THIS error’s NOT related to anything you just “fixed.”  What’s up?  Chances are the creation of what-ever you created worked…..the first time….now when you run it the second time, it fails cause it already exists.  How do you NOT do that in the future?  Here’s the trick, one line, maybe copied twice for good house keeping.

if object_id('BigHonkingTable') is not null drop table BigHonkingTable     --replace BigHonkingTable with what ever you’re checking for

The idea is, BEFORE you create anything, you should/could first check to see if it’s already there.  If it is, I’ve chosen to delete it.  You on the other hand, might chose to use it, your choice.

Here’s a further example.  I’m simply creating a new table, selecting from it and then some house keeping around it to make sure I don’t get any errors at runtime.

if object_id('BigHonkingTable') is not null drop table BigHonkingTable

select c.Table_name, c.Column_Name into BigHonkingTable
from INFORMATION_SCHEMA.COLUMNS c left join sys.Computed_columns comp on c.COLUMN_NAME = comp.name
where c.Table_Catalog = 'Conferences' order by c.table_name, c.Ordinal_Position

select * from BigHonkingTable

if object_id('BigHonkingTable') is not null drop table BigHonkingTable

Ok, so you don’t believe me and think I’m just adding overkill?  Sure, ok.  Comment out the ‘if object…’ lines and then run the query….TWICE!!!!!!!!!!!!!! 

image

AAAAHHHHHHH  Overkill eh?  My way…..you can run it over and over and over and…..you get the idea.

Now that you know how to avoid any types of “object already exists” errors, it’s time to grab a coffee and get coding!

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement