CODEWITHSUNDEEP
sql-server-2008-r2
Kit Z. Fox
Kit Z. Fox

Reputation: 674

Can I exclude/hide tables SQL Server 2008?

I am developing a large database, and recently had to re-structure a portion of it. I created some new tables, but I am reluctant to drop the old ones in case I have broken something that I am not aware of yet. Is there a way to hide these old tables so that they don't clutter up my lists, but yet I have them available to re-use if I need them?

Upvotes: 1

Views: 3053

Answers (2)

K D
K D

Reputation: 31

Perhaps, the new tables names doesn't clash with old ones because you have chosen different names and you don't want to see the original ones to be visible in the object explorer of Management Studio.

You can hide them using the system stored procedure sp_addextendedproperty:

EXEC sp_addextendedproperty 
@name = N'microsoft_database_tools_support', 
@value = <Hide? , int, 1>, 
@level0type = 'schema', 
@level0name = N'<Schema Name, sysname, dbo>',
@level1type = 'table', 
@level1name = N'<Table Name, sysname, ?>'

This way only you (other don't see them) would still be able to query these tables when required.

http://avinashily.blogspot.in/2011/05/how-to-hide-table-in-sql-server.html

Upvotes: 2

Sankar Reddy
Sankar Reddy

Reputation: 1499

they don't clutter up my lists

I guess you are referring to the Object Explorer here. One way to handle is to put those tables in a separate schema something like Junk or any name you like.

ALTER SCHEMA Junk TRANSFER dbo.YourOldTableName

http://msdn.microsoft.com/en-us/library/ms173423.aspx

The other drastic option is to deny SELECT permissions on those old tables to yourself and have a separate login to access them. I don't even like this option :-)

Upvotes: 2

Related Questions