Check the existence of indexes before trying to create them

Here is the standard template to check existence of an index within the database you are working on:

-----------------------------------------------------------------

-- where MyTable and MyIndex are replaced with appropriate values

-- and 'IndexID' is the non-replaced string value actually used

   If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexID') Is Null

              CREATE INDEX index_name ON table_name (column1, column2, ...)

 -- for example:

       If IndexProperty(Object_Id('dbo.TEST'), 'IX_TEST_State', 'IndexID') Is  not Null

BEGIN

        CREATE INDEX [<IndexName>] ON [<DatabaseName>].[<Schema>].[<TableName>] 

        ([column1]) ,,,

END

But what if you're trying to create a stored proc to do this on a database other than where your stored proc exists.... like DB A is loaded every night by restoring a backup off another server and won't retain it's stored procedures when it's overwritten?  Unfortunately, the usual and common method documented above will not traverse to another database and will always report it was not found, thus trying to create the index anyway.  Here is a method that will query the other DB's internals without fail:

IF NOT EXISTS (

        SELECT si.name

        FROM DatabaseName>.sys.indexes si

        INNER JOIN <DatabaseName>.sys.objects so

        ON si.object_id = so.object_id

        WHERE si.name = '<IndexName>'

        AND so.name = '<TableName>'

)

BEGIN

        CREATE INDEX [<IndexName>] ON [<DatabaseName>].[<Schema>].[<TableName>] 

        ([column1]) ,,,

END