sql server - Where does Visual Studio 2013 sql schema compare tool get its view definition from -
so trying compare view definition got location...
select smv.definition view_definition, v.name table_name, iv.is_updatable sys.all_views v join sys.sql_modules smv on smv.object_id = v.object_id join information_schema.views iv on iv.table_name = v.name v.name = @name
also compared against value @ location
select * information_schema.views table_name = @name
now clarity used grab second location turns out truncates definition @ 4000 characters while first query not truncate definition..
but here things both of definitions (assuming short enough not truncate) match. when go sql schema compare tool view definition shows in tool not match view definition stored in either of locations.
give example.
lets view simple dbo.view_test
create view dbo.view_test select db.dbo.tbl_view_test.col1, db.dbo.tbl_view_test.col1 dbo.tbl_view_test go
so lets that's whats showing in sql comparison output visual studio 2013 seems normal...but when go , pull definition either of above queries looks this.
create view dbo.vw_test select db.dbo.tbl_view_test.col1, db.dbo.tbl_view_test.col1 dbo.tbl_view_test go
so somewhere along way view definition got out of whack view...its not supposed vw_test supposed view_test.
so guess i'm wondering schema compare tool pull view definition different returning...and why/how out of sync view. because aren't same.. view in database named view_test how did definition modified else (vw_test) , 2 queries above show difference ever sql compare tool in visual studio looks grabs correct definition.
as can see in attached image. running 2 queries above both pointed @ view in question same database shows name of view not same name view in view definition.
but when use vs2013 schema compare pointed @ teh same database view definition correct anf create view
so find myself wondering..is vs pulling view definition differs 1 return when query sql myself..
update:: have tried of following sql scripts pulled stack overflow answers pull view definition
select * information_schema.views table_name = 'view_disc_join_cc_info_component' go select smv.definition view_definition, v.name table_name, iv.is_updatable sys.all_views v join sys.sql_modules smv on smv.object_id = v.object_id join information_schema.views iv on iv.table_name = v.name v.name = 'view_disc_join_cc_info_component' go select definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound sys.sql_modules object_id = object_id('dbo.view_disc_join_cc_info_component'); go select name, object_definition(object_id) sys.objects type = 'v' , name = 'view_disc_join_cc_info_component' go select object_definition (object_id('dbo.view_disc_join_cc_info_component')) objectdefinition; go select c.text sysobjects o join syscomments c on c.id = o.id o.name = 'view_disc_join_cc_info_component' , o.type = 'v' go
none of above scripts show view defintion create claus matching view name should....i see in microsoft compare tool.
it's result of sp_rename ran on server. in example below, after call sp_rename, name of object changed vw_1 in sys.objects , object explorer, definition syscomments , sys.sql_modules still show view_1. if right click on object in object explorer , script modify, management studio find/replace , fix problem in generated alter script, sounds schema compare doing you.
create view [dbo].[view_1] select dbo.table_1.* dbo.table_1; select * sys.objects name 'v%' select * syscomments text '%view%'; select * sys.sql_modules [definition] '%view%'; sp_rename 'view_1', 'vw_1';
Comments
Post a Comment