TSQL: Check to see if a partition function already exists OR check to see if a partition scheme already exists

When working with TSQL scripts and creating objects, it's typical to drop the object before creating it using a conditional check on it's object identifier. For example:

IF( OBJECT_ID( 'myTable', 'U' ) IS NOT NULL )
BEGIN
 DROP TABLE myTable;
END

The biggest reason for this is to avoid an error. What if the table didn't exist? If you tried to drop it without the conditional expression, you'd get an error and your script would likely halt.

But what if you're trying to drop/create an entity that isn't necessarily an object (i.e., it doesn't have an object identifier and therefore the OBJECT_ID function won't work)? You will definitely need to change your condition up a bit by querying a catalog view.

For partition functions and partitions schemes, you will query sys.partition_functions and sys.partition_schemes.

-- create a paritition function to test this out
CREATE PARTITION FUNCTION PfTest( tinyint )
AS RANGE LEFT FOR VALUES( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );

-- visually inspect the partition functions table to see that it's there
SELECT
 *
FROM
 sys.partition_functions

-- drop the partition function if it exists
IF( EXISTS( SELECT * FROM sys.partition_functions WHERE name = 'PfTest' ) )
BEGIN
 DROP PARTITION FUNCTION PfTest;
END

-- visually inspect the table to see that it's gone
SELECT
 *
FROM
 sys.partition_functions

The EXISTS function basically runs the query to see if there are 0 rows (hence, nothing exists) or 1+ rows (hence, something exists). If the sub-query returns a single row that matches the name of PfTest, then the condition is true and the partition function will be dropped. If it doesn't exist, then it will not be dropped.

Another option -- if you prefer cleaner and more readable TSQL -- is to create some custom functions, something like PARTITION_FUNCTION_ID or PARTITION_SCHEME_ID.