带有sysobjects表的动态视图

Question

我想使用to脚本动态修改视图。每个月我在我的数据库中都有一个新的表，我想在我的视图中包含新的表。我的想法是在then过程中创建一个var，然后构建sql语句来创建用于更改视图的代码。有了这个，我只需要执行(@SqlView)。现在的挑战是获取字符串中的(@SqlResults)。有什么想法吗？

SQL for the view (@SqlView)
select a, b from table01
union all
select a, b from table02
union all
select a, b from table03

SQL statement for the view code (@SqlResults)
select 'select a,b from '+so.name' union all' from    sysobjects so
join    sys.schemas s
        On  so.uid = s.schema_id
where   so.xtype = 'U'
and so.name like '%table0%'

Answer 1

下面是另一种不使用循环的方法。

declare @SqlResults nvarchar(max) = ''

select @SqlResults = @SqlResults + 'select a,b from ' + t.name + ' union all ' 
from sys.tables t
where t.name like '%table0%'

select @SqlResults = 'ALTER VIEW SomeView as ' + left(@SqlResults, LEN(@SqlResults) - 10)

select @SqlResults
--Uncomment the exec line when you are comfortable
--exec sp_executesql @SqlResults

Answer 2

这是我在试图为每个表生成脚本时使用的SQL，这些脚本被修改为插入特定的详细信息：

DECLARE @SQLTable TABLE
  (
    ID INT IDENTITY(1,1) NOT NULL,
    Sequel VARCHAR(4000) NOT NULL
  )

INSERT INTO @SQLTable (Sequel)

SELECT 
'SELECT A, B FROM ' + TABLE_NAME + ' UNION ALL'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%table0%'



DECLARE @Looper INT = (SELECT MAX(ID) FROM @SQLTable)
DECLARE @Counter INT = 0 
DECLARE @ExecSQL VARCHAR(MAX) = 
    --'' -- use this if you just want to run the SQL
    'ALTER VIEW MyView AS 
    ' -- use this if you want to alter a view

WHILE @Counter <= @Looper 
  BEGIN
    SET @Counter = @Counter + 1
    SELECT @ExecSQL = @ExecSQL + '
    ' + Sequel 
    FROM @SQLTable
    WHERE ID = @Counter 


  END


SET @ExecSQL = 
    LEFT (@ExecSQL, LEN(@ExecSQL) - 10) -- the LEFT() function is to remove the final UNION ALL
    + '

GO'
PRINT (@ExecSQL)

Answer 3

也许有一种方法可以用游标来实现这一点，但我对这个语法并不太熟悉。我在类似情况下所做的是使用几个变量，或者一个表变量或者一个临时表。我也没有太多的机会使用动态sql，所以这是一种暗箱操作。

declare   @view_script as varchar(4000) = 'alter view dbo.my_view as select a, b from table01'
declare   @cur_table as integer = 2
declare   @table_list as table (table_num integer identity(1,1), table_nm varchar(100))

insert into @table_list (table_nm)
select name
from   sys.tables
where  name like '%table0%'

while  @cur_table <= (select max(table_num) from @table_list)
begin
select @view_script = @view_script + 'union all ' + char(10) + select a, b from ' + table_nm
from   @table_list
where  @table_list.table_num = @cur_table

@cur_table = @cur_table + 1
end

exec @view_script

让我知道，如果这是可行的，或如果有什么东西，你必须改变，以使它发挥作用。