Monday, May 9, 2011

How to hide a table in sql server management studio.

I always wanted to hide a table which is critical to be displayed to the end user who might be using management studio for some purpose. recently i came a across a property indeed an extended property introduced in sql server 2005 called sp_addextendedproperty. i found it interesting. so, i have searched books online (bol) for the usage and i found that i could hide the table from this procedure. i am posting the procedure to do this.

suppose you have a database mycompany  schema users in which you have a table with important information containing telephone numbers, address and many more details table emp_details. you could hide the table from viewing in management studio with the help of sp_extendedproperty.

usage:

EXEC sp_addextendedproperty 
@name = N'microsoft_database_tools_support', 
@value = '<Hide? , sysname, 1>', 
@level0type ='schema', 
@level0name ='users',  --give the schema name here. if it is dbo give 'dbo'
@level1type = 'table', 
@level1name = 'emp_details'  --give the table name which you want to hide.
 

hope you enjoyed it....
please comment...




4 comments:

  1. Great, just what I was looking for.
    Thanks very much Avinash!

    ReplyDelete
  2. Nice, but dbDefence can hide all database schema completely even from DBA

    ReplyDelete
  3. Thanx much for posting this solution. It fit my need perfectly.

    One question: is there a value for un-hide? The only thing I've been able to figure out is to drop the extended property which works, but I would prefer to just to an update.
    TIA

    ReplyDelete
  4. Its working grate but how can i see the hide table

    ReplyDelete