CODEWITHSUNDEEP
sql-server
Sajjad Abdullah
Sajjad Abdullah

Reputation: 47

Hide some tables in SQL Server 2014 Object Explorer

Is it possible to hide (and show) some tables in object explorer of SQL Server 2014? Actually there are repetitive log tables which are prefixed with actual table. I want to hide them temporarily. Also when needed I want to show them back.

Upvotes: 1

Views: 897

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

you can use extended properties

EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

Above code hides person.address table

If you want to revert,below is the code

EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

References: Hiding tables in SSMS Object Explorer

You also can use below to deny viewing any user particular schema or table in object explorer,but this doesn't work when user is of higher role like db_owner..

DENY VIEW DEFINITION ON Schema.Table TO UserName;

Upvotes: 2

Bhargav Chudasama
Bhargav Chudasama

Reputation: 7161

try this one

if you want to hide table use this one

EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

and show table use this one

EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

Upvotes: 1

Emo333
Emo333

Reputation: 136

You can add an extended property which will hide the table:

EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = '**YOUR SCHEMA**',
@level1type = N'Table', @level1name = '**YOUR TABLE NAME**';
GO

Upvotes: 0

Related Questions