Tuesday, May 9, 2017

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!

No comments:

Post a Comment