How to Drop a Certificate Conditionally with CERT_ID

Introduction

Sometimes you need to be able to determine if a particular certificate exists already. For example, automated deployment scripts are a place where this functionality is commonly required. You could use the sys.certificates catalog view to get that information. But there is an even simpler way: CERT_ID().

CERT_ID() Example

The built-in CERT_ID() function takes one parameter, the name of the certificate. Its primary function is to return SQL Server's internal certificate_id for the certificate specified in the name parameter:

The output of this function is an integer value:

This integer value represents the certificate_id, if a certificate with that name exists in the current database. Otherwise, NULL is returned. That fact however we can use to check for existence.

If we want to drop a certificate automatically if it exists, for example as part of a deployment script, we can use CERT_ID in an IF statement like this:

If the certificate ACertificate exists, CERT_ID returns a non-NULL value and the DROP statement is executed. If it does not exist, the DROP statement is skipped.

Summary

SQL Server's built-in CERT_ID() function returns the internal certificate_id of a certificate in the current database. Since it returns NULL if the specified certificate does not exist, it can be used to execute statements conditionally based on the existence of a certificate.