Checking if an object exists in SQL Server

Recently I was trying to remember the code to check and see if a procedures exists before I do something with it and I did a search on the internet to see what came up. The most common response that I saw was:

view plain print about
1if exists (
2 select * from dbo.sysobjects
3 where id = object_id(N'[dbo].[usp_my_dummy_proc]')
4 and OBJECTPROPERTY(id, N'IsProcedure') = 1
5 )
6    select 'IT EXISTS!';
Looking at this code, a couple of things are happening, first the object id is collected and then used to find the object in sysobjects. Next it is validating that it is a procedure.

For my purposes though, this was overkill. I was already creating a procedure using a template and one of the last steps I wanted to do was change the permissions. However if there is an error creating the procedure I didn't want the permission change to generate another error. In this case, I didn't need to check if it was a procedure because I was creating it right then. As I looked at the code, I also noticed something else, there are two hits being made just to check for existence when all that is needed is one. I say there are two because object_id(N'object_name') will return an id if something exists and a null if it doesn't.

Therefore, you can save on excess processing if you just want to know if something is there by doing the following:

view plain print about
1IF (OBJECT_ID(N'[dbo].[usp_my_dummy_proc]') IS NOT NULL)
2 SELECT 'IT EXISTS!';

Just another little code snippet to help when you need to just know if it exists.

Finding ports for SQL Server Named Instances

Today I needed to make a connection to a named instance of a Microsoft SQL Server 2008 R2 database, however I could not use the named instance in the application I was using. Instead, server name and port needed to be used.

Important things to note: * Each named instance is assigned it's own port number. * The table sys.dm_exe_connections has this port information.

The following query will give you the port number if you are already connected to the database:

SELECT DISTINCT LOCAL_TCP_PORT FROM sys.dm_exec_connections WHERE LOCAL_TCP_PORT is not null;

I have heard a rumor that there are some configurations of SQL server that would allow the server to assign random ports on reboot, but I have not been able to verify that. It should just be considered that there is a possibility that if the server is rebooted you may have to rebuild any connections using the port number.