IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND TABLE_NAME = 'TheTable')) BEGIN --Do Stuff END
IF EXISTS (SELECT object_id FROM sys.tables WHERE name = 'Artists' AND SCHEMA_NAME(schema_id) = 'dbo') PRINT 'The table exists' ELSE PRINT 'The table does not exist';