CREATE OR ALTER – In SQL Server 2016..A Brand New Syntax :)

From SQL Server 2016 onward, you don't have to check for the existence of the object with the help of IF…ELSE… conditional statement and decide to execute either the CREATE or ALTER statement in the respective code block. Because, Microsoft introduces a New syntax that will help us to avoid the additional pain and also save time in writing all these logic.

This new statement combines CREATE and ALTER statements and creates object if it does not exist, or alter it if it is already there. 

CREATE OR ALTER can be applied on the following object:

STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

Syntax:

CREATE OR ALTER object_name

Now let's see the same with an example. Suppose we have a task where we want to modify an object i.e. Stored Procedure and we are not sure if it already exists on the instance. Then earlier we were writing code as below:


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]'))  
    EXEC sp_executesql   
        N'CREATE PROCEDURE [dbo].[Test]  
        AS  
        BEGIN  
            SET NOCOUNT ON;  
            PRINT ''Hello Readers'' 
        END'  
ELSE  
    EXEC sp_executesql   
        N'ALTER PROCEDURE [dbo].[Test]  
        AS  
        BEGIN  
            SET NOCOUNT ON;  
            PRINT ''Hello Readers''  
        END'    

Now let's see how can we replace the above code with the help of new syntax:

CREATE OR ALTER PROCEDURE [dbo].[Test]  
AS  
BEGIN  
    SET NOCOUNT ON;  
    PRINT 'Hello Readers'  
END 


It looks like this statement is going to help us a lot.

Thank You!

How to Hide SQL Server table, view and stored procedure in SSMS..

We can hide the SQL server tables, view and stored procedure in SQL Server Management Studio by using the using the system stored procedure "sp_addextendedproperty".


Syntax:
sp_addextendedproperty
    [ @name = ] { 'property_name' }
    [ , [ @value = ] { 'value' }
        [ , [ @level0type = ] { 'level0_object_type' }
          , [ @level0name = ] { 'level0_object_name' }
                [ , [ @level1type = ] { 'level1_object_type' }
                  , [ @level1name = ] { 'level1_object_name' }
                        [ , [ @level2type = ] { 'level2_object_type' }
                          , [ @level2name = ] { 'level2_object_name' }                 ]       ]        ]     ] [;]

Arguments:

[ @name ] = { 'property_name' } Is the name of the property to be added. property_name is sysname and 
cannot be NULL. Names can also include blank or non-alphanumeric character strings, and binary values.

[ @value= ] { 'value'} Is the value to be associated with the property. value is sql_variant, with a default of NULL. 
The size of value cannot be more than 7,500 bytes.

[ @level0type= ] { 'level0_object_type' }  Is the type of level 0 object. level0_object_type is varchar(128),
 with a default of NULL. Valid  inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, 
MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, 
ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, and NULL.

[ @level0name= ] { 'level0_object_name' }

    Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL.
[ @level1type= ] { 'level1_object_type' }

    Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs 
are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, 
VIEW, XML SCHEMA COLLECTION, and NULL.
[ @level1name= ] { 'level1_object_name' }

    Is the name of the level 1 object type specified. level1_object_name is sysname, with a default of NULL.
[ @level2type= ] { 'level2_object_type' }

    Is the type of level 2 object. level2_object_type is varchar(128), with a default of NULL. Valid inputs are COLUMN, 
CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.
[ @level2name= ] { 'level2_object_name' }

    Is the name of the level 2 object type specified. level2_object_name is sysname, with a default of NULL.

Now let see the same with an example. We have a database Test with table "ID". Below is the screen shot of same:

Let's assign the table property by executing the below SQL query:

EXEC sp_addextendedproperty

@name = N'microsoft_database_tools_support',

@value = 'Hide',

@level0type = N'Schema', @level0name = 'dbo',  --Schema Name

@level1type = N'Table', @level1name = 'ID';  --Table Name

GO

Now Refresh the tables and see the list again. BINGOOOO. The table is not visible now.

Now let's rollback the activity and make it visible again by executing the below SQL statement. 

EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'schema', @level0name = 'dbo', --Schema Name
@level1type = N'Table', @level1name = 'ID'; --Table Name
GO

Refresh the list and we can see the table in list again. Similar way you can hide view, stored procedure and functions etc..

Thank You!