I spotted a question over at Stack Overflow asking whether it is possible to hide specific SQL Server user-tables from end users. Strictly speaking it is not, but there is a trick to get superfluous tables out of the way of the "Tables" node of the object explorer. While, on first glance, this may seem like poor practise, it turns out that there's a bona-fide case for it: Garbage tables used in table partitions.
A data warehouse that I recently delivered makes extensive use of SQL's partitioned tables feature - which enabled a data-load design which does incremental fast-loads... something which simply isn't possible without partitions.
The down-side of using partitions is that, in order to keep a fact table on-line whilst loading in new data, you effectively need three tables:
- The fact table
- A staging table, into which new data is loaded and prepared
- A garbage table into which the old partition of the fact table can be switched
The garbage table is of absolutely no value other than as a means for nuking a data partition, and so there's really very little value in cluttering Management Studio's Tables tree with these tables.
The answer? Mark the garbage tables as "database tools support" tables. This causes SSMS to hide the tables from normal view and instead list them under the "System Tables" node. Nice'n'neat!
Here's a script template which you can add to your template explorer:
@name = N'microsoft_database_tools_support',
@value = '',
@level1type = 'table',
@level1name = N'.
Either add it to your template explorer, or simply paste into a query window, then press CTRL+SHIFT+M to launch the "specify values..." dialog.