Determine if table or stored procedure exists in database

I'm currently putting the finishing touches to a plugin. It requires the creation of a table and several stored procedures. Is there an accepted method of interrogating the database to see if these objects already exist?

Since SQL 2016, you can add IF EXISTS to a DROP statement, so that takes care of removing the objects when Uninstalling the plugin.

However, if you try to CREATE an object that already exists, an error will occur.

I need to be able to see if they already exist.

What I've tried -
Adding the test within the ExecuteSqlScript SQL. This doesn't work for stored procedures because the CREATE statement must be the first statement. You can't do a test to start with.

ExecuteSqlCommand returns an integer of the number of records affected. This is only for UPDATE, CREATE and DELETE commands.

Any suggestions would be welcome.

Thanks in advance.
Paul

For Procedures,  Create or Alter[/url] ...  (as of 2016 SP1)

create or alter procedure Test as
begin
print (1);
end;


For Tables

if not exists (select * from sysobjects where name='MyTableName' and xtype='U')
    create table MyTableName (
        MyColumn varchar(64) not null
    )

Hi New York
Wasn't aware of the CREATE OR ALTER statement implemented in 2016 SP1. I updated my code and attempted to run it, but to no avail.
On investigation, my hosting supplier implements SQL 2016 but haven't upgraded to SP1 yet. It is shared hosting, so I can't see them rushing into upgrading just for me.
I have taken the code back to calling my "DeleteSQLObjects" code before creating them in the Install routine. It doesn't feel good, but it works.

Many thanks for your response and stay well
Paul

I'm curious ...; why are you using stored procs?  Linq queries can work very well.

The plugin is a decision support/reporting system using product/category/manufacturer images. It will grab the product catalog or order sales as a whole. These are not straight forward queries. A couple of them need temporary tables and they use group by's and calculations. It's just a great deal faster to use stored procs as opposed to linq.

So there could be a few thousand records returned. My test data returns and processes about 5000 records in about 6 seconds. Speed is paramount.

I think you'd have to see an example to get a feel for what it's doing. If you're interested, Private Message me and I'll send you a link to my test site with login details and some instructions on usage.