December 20, 2006 at 5:21 pm
Is there any way to display views as well as tables in the database diagram?
My users access the database through views only and I would like to make a diagram for them to show relationships between the views. But the 'add table' dialog only gives me tables to choose from. Thanks!
December 20, 2006 at 6:11 pm
I don't think it possible to add views to database diagrams..
MohammedU
Microsoft SQL Server MVP
December 21, 2006 at 8:31 am
I use Visio (as part of MS Office 2003). It will get all the details from SQL-Server itself although it works better with 2000 than 2005.
Choose New -> Database -> Database Model Diagram. An extra Database menu iteam appears from this select Reverse engineer. Follow the wizard through you can include Views and stored procedures. However you will have to add and links manually for views.
December 21, 2006 at 2:53 pm
Thanks for the tip on using Visio, but unfortunately I don't think my workplace has a license for it (although I'm checking into that). I think it's sold separately from the rest of the Office 2003 suite, but please correct me if I'm wrong.
Still searching for a way to diagram views within SQL server.....
December 21, 2006 at 3:03 pm
As I mentioned before I don't think there is any way within the sql...
If you find the way, please share with the forum..
MohammedU
Microsoft SQL Server MVP
December 21, 2006 at 3:23 pm
It's not that I don't believe you, Mohammed, I'm just hoping that there is a way.
I thought I read somewhere that you can include views in the diagram, but you have to add the views to the 'data source' (or something like that) in a SQL server 2005 project. I didn't quite understand how to do it so I thought I would see if anyone else out there has figured it out. I was looking into this a couple of months ago so I'll try to go back and find where I read that and see if it makes any more sense this time. If I do figure it out I will definitely share the info here.
December 22, 2006 at 9:45 am
Approach 1: Create a separate database and create tables with the same structure as the views. (Hint: "SELECT * INTO View FROM OtherDB..View WHERE 1=0" works great, and is easily scriptable.) You could create primary and foreign keys that correspond to the underlying table structures if you want them to show up in the diagrams. Then create all the diagrams you like.
Approach 2: Use the Query Designer. It allows you to add views, and you can turn off the SQL and Criteria panes leaving only the Diagram pane. When you have the diagram you like, use ALT-PrtScn to copy it and paste it in Paint, Word, or some other image application.
December 22, 2006 at 12:52 pm
Thank you for the suggestions, Scott. I played around with both ways and I think I prefer Approach 1. However now I have another question because I'm not allowed to create a separate database on the same server, but I can create one on a different server. So I tried:
SELECT * INTO View FROM OtherServer.OtherDB..View WHERE 1=0
but I get this error message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
If I want to create a script for this anyway, how can I add my login info to the script so it lets me into the other server? (Maybe I should start a new thread for this question??) Thanks!
December 26, 2006 at 8:31 am
You can't add login info to the script for Windows logins, you have to either get security to work (probably with help from the admins) or try another approach.
It sounds like there is a linked server definition on the target server for the remote server but you do not have rights to use it. The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON" message may also mean that your domain is not using Kerberos authentication properly for delegation, or there may be other security issues. Both problems can only be addressed by a DBA and/or network admin.
You might use "SELECT * INTO View FROM OPENDATASOURCE('SQLNCLI','Data Source=<server>;Integrated Security=SSPI').<database>.<schema>.View WHERE 1=0", as this should work without a linked server definition if you have login rights on the remote server. If you still get the anonymous logon error, it is probably due to network security or Active Directory issues. OPENDATASOURCE can also fail if the server is configured to disallow ad hoc access.
You could use the Management Studio Export Data function to copy the view definitions. Use the "Write a query option to specify the data to transfer" and use "SELECT * FROM View WHERE 1=0" queries. The destination table name will default to Query so you will have to manually change it to the view name. Since you are connecting to both servers from your PC instead of asking the servers to talk to each other you shouldn't have any security errors.
Another approach would be to use Access. Create a link to the SQL Server view, which will be named dbo_View (if the view is in the dbo schema). Then use Create Table queries such as "SELECT * INTO View FROM dbo_View WHERE 1=0" to create empty tables. You can then delete the external links. When finished, you can either do your diagramming in Access or import all the tables from Access into you destination SQL Server database and diagram there.
January 3, 2007 at 5:08 pm
Thank you for all of the suggestions, Scott! I tried using OPENDATASOURCE, but still get the login error message. Exporting from management studio works, but is slow since I can only export one view at a time with this approach. Similarily, the Access approach is also time consuming since I would have to make a Create Table query for every view. Currently we have about 25 views, but our database is still growing so I wanted a solution with maximum flexibility.
I found out how to add views to a diagram using the Business Intelligence Development Studio. The steps are:
1. Create a project and solution.
2. Add a data source to the project (connection to the database)
3. Add a data source view - this is where you can bring in tables and/or views that can be displayed in the diagram.
You can then create relationships between the views and also add or remove tables and views easily.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply