Tuesday, February 27, 2001 ..:: Home ::.. Register  Login
   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 =
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!!!!!!!!!!!!!! 


AAAAHHHHHHH  Overkill eh?  My way… can run it over and over and over and… 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!

Location: Blogs Parent Separator TechTidBits

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