object_id() vs sys.objects

Question

I use database scripts where I check for the existence of a Stored Procedure then drop it then create it.

Which of the following would be more efficient for checking and dropping SPs
Option 1

IF EXISTS(SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[myStoredProc]',N'P'))
  DROP PROCEDURE dbo.myStoredProc;

Option 2

IF OBJECT_ID (N'dbo. myStoredProc',N'P') IS NOT NULL
  DROP PROCEDURE dbo.myStoredProc;

I have decided to use the second one due to obvious reasons, is there any reason why I should go for the first option

score 3 · Answer 1 · answered May 16 '16 at 13:25

3

The tide has changed. Current policy from MSDN is that sys.objects should be used instead of INFORMATION_SCHEMA views, because the sys views have more information than the latter.

answered May 16 '16 at 13:25

Ross Presser

6,027
1
34
66

score 1 · Accepted Answer · answered Apr 28 '09 at 05:46

No, there are no compelling reasons to use sys.objects directly. As a matter of fact, use of these sys views is being discouraged - so if you can avoid it, do so!

Instead, INFORMATION_SCHEMA schema views are supposed to be used where possible - this is a standard SQL-92 mechanisms for exposing metadata about your server (rather than a Microsoft-specific way of using the sys.* views).

Marc

object_id() vs sys.objects

2 Answers2