-1

I want to use one statement for create and alter both.

I use CREATE TABLE if the object doesn't exist, and if object does exist, I use ALTER TABLE. How to do that on condition for database objects (stored procedures, views, tables, schemas, custom types and etc.)

Like for tables

MODIFED

CREATE TABLE MyTable *****

ALTER TABLE MyTable *****

Above are two different statements, if MyTable Exists CREATE raise error similar if does not exsits ALTER raise error so am trying to resolve this error by one statement like "oracle create replace" and oracle do not gives error and self Create/replace.

is there any sql command syntax ?

Haseeb
  • 746
  • 7
  • 22
  • share your current code snippet of what you are actually trying to do. – DarkRob Mar 04 '19 at 04:22
  • @DaleBurrell not duplicate I am looking a statement like oracle "create replace table" – Haseeb Mar 04 '19 at 04:36
  • 1
    As of SQL Server 2016 **SP1** - you can use [`CREATE OR ALTER`](https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/) for all your statements – marc_s Mar 04 '19 at 04:55
  • @Haseeb thats not at all clear from your question - maybe you can reword it. – Dale K Mar 04 '19 at 05:05
  • @DaleBurrell modified pls – Haseeb Mar 04 '19 at 07:35
  • @marc_s 1+ thanks let me check in sql2016 but is that available for 2012 as well? – Haseeb Mar 04 '19 at 07:36
  • @Haseeb: no it's **only** available for SQL Server **2016 SP1** and newer - not for 2012 – marc_s Mar 04 '19 at 07:37
  • @marc_s yes it work with SQL 2016 thanks, you did not answer but this what i was looking for you may answer i will mark my ansewr – Haseeb Mar 04 '19 at 14:02

1 Answers1

2

Can you try like this For a table 

IF  EXISTS (SELECT 1
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_NAME = 'Table_name'  
                      AND TABLE_SCHEMA='DBO')
  BEGIN

     create table SYNTAX

ALTER TABLE SYNTAX
  END
GO

For a Column Checking 

IF  EXISTS (SELECT 1
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_NAME = 'Table_name'
                      AND COLUMN_NAME = 'Column_name'
                      AND TABLE_SCHEMA='DBO')
  BEGIN

      ALTER TABLE Table_name
        ALTER COLUMN Column_name DATATYPE
  END
GO
Chanukya
  • 5,833
  • 1
  • 22
  • 36