如何选择 * 但没有“列名在每个视图中必须唯一"
我需要封装一组我们经常在供应商的数据库服务器上使用的表 JOIN.我们在提取等的许多地方重用了相同的 JOIN 逻辑，似乎 VIEW 允许在一个地方定义和维护 JOIN.
I need to encapsulate a set of tables JOINs that we freqently make use of on a vendor's database server. We reuse the same JOIN logic in many places in extracts etc. and it seemed a VIEW would allow the JOINs to be defined and maintained in one place.
CREATE VIEW MasterView
AS
SELECT *
FROM entity_1 e1
INNER JOIN entity_2 e2 ON e2.parent_id = entity_1.id
INNER JOIN entity_3 e3 ON e3.parent_id = entity_2.id
/* other joins including business logic */
etc.
问题在于供应商对数据库进行了定期更改(列添加、名称更改)，我希望这些更改自动反映在MasterView"中.
The trouble is that the vendor makes regular changes to the DB (column additions, name changes) and I want that to be reflected in the "MasterView" automatically.
SELECT * 允许这样做，但基础表都有 ID 列，所以我收到每个视图中的列名必须唯一"错误.
SELECT * would allow this, but the underlying tables all have ID columns so I get the "Column names in each view must be unique" error.
我特别想避免列出表中的列名，因为 a) 它需要频繁维护 b) 每个表有数百列.
I specifically want to avoid listing the column names from the tables because a) it requires frequent maintenance b) there are several hundred columns per table.
有什么方法可以实现 SELECT * 的动态但有效地排除某些列(即 ID 列)
Is there any way to achieve the dynamism of SELECT * but effectively exclude certain columns (i.e. the ID ones)
谢谢
推荐答案
我最终采用了这个方法，基于 Madhivanan 的建议.它类似于 t-clausen.dk 后来建议的(感谢您的努力)，尽管我发现 xml 路径样式比游标/等级分区更优雅.
I had gone with this in the end, building off of Madhivanan's suggestion. It's similar to what t-clausen.dk later suggested (thanks for your efforts) though I find the xml path style more elegant than cursors / rank partitions.
以下在运行时重新创建 MasterView 定义.基础表中的所有列都以表名开头，因此默认情况下我可以在视图中包含两个名称相似的列.仅此一项就解决了我原来的问题，但我还包含了WHERE column_name NOT IN"子句来专门排除某些永远不会在 MasterView 中使用的列.
The following recreates the MasterView definition when run. All columns in the underlying tables are prepended with the table name, so I can include two similarly named columns in the view by default. This alone solves my original problem, but I also included the "WHERE column_name NOT IN" clause to specifically exclude certain columns that will never be used in the MasterView.
create procedure Utility_RefreshMasterView
as
begin
declare @entity_columns varchar(max)
declare @drop_view_sql varchar(max)
declare @alter_view_definition_sql varchar(max)
/* create comma separated string of columns from underlying tables aliased to avoid name collisions */
select @entity_columns = stuff((
select ','+table_name+'.['+column_name+'] AS ['+table_name+'_'+column_name+']'
from information_schema.columns
where table_name IN ('entity_1', 'entity_2')
and column_name not in ('column to exclude 1', 'column to exclude 2')
for xml path('')), 1, 1, '')
set @drop_view_sql = 'if exists (select * from sys.views where object_id = object_id(N''[dbo].[MasterView]'')) drop view MasterView'
set @alter_view_definition_sql =
'create view MasterView as select ' + @entity_columns + '
from entity_1
inner join entity_2 on entity_2 .id = entity_1.id
/* other joins follow */'
exec (@drop_view_sql)
exec (@alter_view_definition_sql)
end
相关文章