How to check if an object exists in a SQL 2005 Database

When executing some SQL code on an object in database such as a table, stored procedure, or view, it is important to check to see if these objects currently exist in the database. It is important because it makes your code more reusable, especially if multiple developers will be running it on their local databases. Here is a simple line of SQL you can run to find various objects in a SQL 2005 database.

How to check if an object exists in a SQL 2005 Database

if exists (select * from sys.objects where [object_id] = object_id(‘the_name_of_your_object’))
begin
print ‘Hello World’
end


How to check if a stored procedure exists in a SQL 2005 Database

Say your Database contained several hundred stored procedures and you wanted to know if the stored procedure usp_GetNames existed. Simply run the following example from above:

if exists (select * from sys.objects where [object_id] = object_id(‘dbo.usp_GetNames’) )
begin
print ‘Hello World’
end

How to check if a view exists in a SQL 2005 Database

What if you wanted to create a new view called dbo.vw_OldCustomers, but wanted to see if that view existed before you created it? Again, run the same SQL code above.

if exists (select * from sys.objects where [object_id] = object_id(‘dbo.vw_OldCustomers’) )
begin
— Logic to create the view and populate it
end


-Flea#

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: