Compare and script views
vanram
Posts: 64
SQL 2000:
I have a view that is owned by user/schema that is not dbo. When it compares it finds the version in the 2 databases as being the same BUT if i go to the "=" section i find the create line is different and marked different. 1 side does NOT have the explicit schema reference. When it is comiled it will do so to dbo, it succeeds as all referenced objects in the view are explicit.
How do i get "SQL Compare" to either re-create the compare script, on the side where it is not explicit based on known Owner/schema or to treat it as "different" so that i can update all such circumstances and be sure all such situations in DB fixed!
Thanks
Marcus
I have a view that is owned by user/schema that is not dbo. When it compares it finds the version in the 2 databases as being the same BUT if i go to the "=" section i find the create line is different and marked different. 1 side does NOT have the explicit schema reference. When it is comiled it will do so to dbo, it succeeds as all referenced objects in the view are explicit.
How do i get "SQL Compare" to either re-create the compare script, on the side where it is not explicit based on known Owner/schema or to treat it as "different" so that i can update all such circumstances and be sure all such situations in DB fixed!
Thanks
Marcus
Comments
After a brief look into this, I don't see a 100% solution for recreating all views that don't have an explicit owner defined. A crude way of doing this would be to script all of the views using SQL Server Management Studio (I believe it adds the owner to the creation script for you) and drop all of the views and run the SQL to replace them again.
The only problem may be if there are dependencies between views -- then you may be able to use SQL Compare to create a script just to work out which order to run the scripts in. SQL Compare isn't automatically going to 'fix' the inplicit ownership, though.
Thank you for identifing a work around. I have already dealt with it manually.
I posted it so your team might be able to identify a way to enhance your tools to handle this situation...
A chance for improvement...
Thanks
Marcus