SSMS 2008 - Is there a way to better display tables in various schemas

Question

SSMS 2008 - Is there a way to better display tables in various schemas

deepmustang 90114

Mr or Mrs. 500

Points: 548
More actions
April 8, 2011 at 12:46 pm

#249118

In sql server 2008 we have 30 users schemas setup under a singel database (say "UserDB")
Each user is set up with access to his/her own schema
So when you login to server and drill down to Databases >>UserDb >> Tables, you will see the tables that belong to your schema
Now I have to give cross Schema access. User 1 wants to see User 2's schema tables.
User 1 tables shows up as user1.table1 and user 2 tables show up as user2.table2
In SQL server 2008 we have 30 users schemas setup under a single database (say "UserDB")
Each user is set up with access to his/her own schema
So when you login to server and drill down to Databases >>UserDb >> Tables, you will see the tables that belong to your schema
Now I have to give cross Schema access. User 1 wants to see User 2's schema tables.
User 1 tables shows up as user1.table1 and user 2 tables show up as user2.table2
Question is: Instead of listing ALL the tables, differentiated by prefix, is there a way to show them like windows folders.
Example:
Parent folder (Tables)
- Child Folder 1 (User1 Schema)
- Child Folder 2 (User2 schema)
This is purely to group the tables for better visual representation (and no technical reason behind it)
OR
Is there a better way to visually show tables from multiple schemas under the same database ?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Are you asking how to get a list of them or how best to display it in your application? If it is the first, you already have that knowledge from the listing by user. If it is the second, that is a UI decision that would be based on the type of application, who the consumers are, what language it is written in etc...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deepmustang 90114 Mr or Mrs. 500 Points: 548 More actions · Answer 2

My question was how do you display (group) the Schema tables with a treeview kind of look in the SSMS 2008 (not any custom application). Also I am not trying to script them.

See the link below.

https://connect.microsoft.com/SQLServer/feedback/details/580903/add-ability-to-add-folders-groups-to-logically-sort-tables-in-ssms

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Now I see what you are asking. I don't know of any way to list tables like that but it does seem like it could be a good thing in some situations. Base level would be the schemas and then you could drill down into the objects for each schema. I don't have enough schemas in any of our systems that would be useful for me but I can see where it would be. Maybe somebody will come back with an answer. sorry I can't be any more help.:hehe:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLTreeo SSC Enthusiast Points: 177 More actions · Answer 4

Or try SSMS add-in SQLTreeo. It enables you to organize tables, databases or stored procedures in your custom folders.

__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In