本文介绍了如何为数据库中的所有表创建视图?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
限时送ChatGPT账号..我想从数据库中已经存在的所有表中创建视图,这是一项艰巨的任务,要一张一张地捕获表并使创建视图 XXXX 为 select * from Table_name .我发现使用游标可以实现的东西,代码是:
I want to make views from all tables already exist in database that’s hard task to catch the tables one by one and make create view XXXX as select * from Table_name .I find something that it is possible with cursor and the code is :
DECLARE @TableName sysname
DECLARE @ColumnCount INT
DECLARE @ColumnID INT
DECLARE @SelectColumn NVARCHAR(500)
DECLARE @sql NVARCHAR(max) = ''
DECLARE QUERYINFO CURSOR FOR
SELECT
t.name AS TableName,
ccount.ColumnCount,
c.column_id AS ColumnID,
CASE WHEN c.column_id <> ccount.ColumnCount
THEN c.name + ', '
ELSE c.name
END AS SelectColumn
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id=c.object_id
INNER JOIN (
SELECT object_id,COUNT(*) AS ColumnCount
FROM sys.columns
GROUP BY object_id
) ccount ON t.object_id = ccount.object_id
ORDER BY t.Name,c.column_id
OPEN QUERYINFO
FETCH NEXT FROM QUERYINFO INTO @TableName,@ColumnCount,@ColumnID,@SelectColumn
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColumnID = 1
BEGIN
SET @sql = 'CREATE VIEW v_' + @TableName + ' AS SELECT ' + @SelectColumn
END
ELSE
BEGIN
SET @sql = @sql + @SelectColumn
END
IF @ColumnID = @ColumnCount
BEGIN
SET @sql = @sql + ' FROM ' + @TableName
EXEC sys.sp_executesql @sql
SET @sql = ''
END
FETCH NEXT FROM QUERYINFO INTO @TableName,@ColumnCount,@ColumnID,@SelectColumn
END
CLOSE QUERYINFO
DEALLOCATE QUERYINFO
但它有错误,我不知道如何解决它
but it has error and I don't know how solve it
推荐答案
这就是我对动态 SQL 所做的;首先检查视图是否存在,然后使用所有列创建它(不是使用 SELECT * FROM...
).
This is what I would do with dynamic SQL; checking first to see if the view exists, then creating it with all of the columns (not by using SELECT * FROM...
).
DECLARE @SQL nvarchar(MAX)
SET @SQL = N''
SELECT
@SQL = @SQL +
N'IF EXISTS(SELECT 1 FROM sys.objects WHERE name = N''v_' + t.name + N''' AND type = N''V'') BEGIN DROP VIEW [v_' + t.name + '] END CREATE VIEW [v_' + t.name + N'] AS SELECT ' +
STUFF(
(SELECT N',' + c.name
FROM
sys.columns AS c
WHERE
c.OBJECT_ID = t.OBJECT_ID
ORDER BY
column_id
FOR XML PATH(''), TYPE).value('.',N'nvarchar(max)')
,1,1,N'')
+ N' FROM [' + t.name + N'];'
FROM
sys.tables AS t
EXEC sp_executesql @SQL
这篇关于如何为数据库中的所有表创建视图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
更多推荐
[db:关键词]
发布评论