SQL Server之自动创建视图

本方法只适合特定模式的视图创建.

比如,创建需要整张表列名的视图,或者当表和需要的列名统计在一张数据表当中,如图所示:

 

 

首先要先获取要创建视图所需要的表,这里我获取的是整个数据库中的表,

IF OBJECT_ID('tempdb..#tablename') IS  NOT NULL
BEGIN
    DROP TABLE #tablename
END

SELECT name,ROW_NUMBER() over (order by name) id 
INTO #tablename
FROM [sys].[tables] AS q 

这里通过Row_NUMBER()来给每一列返回一个数字ID,在下面可以通过这个ID来循环获取表名.

接着来拼接视图的名称,,名称可以通过实际的需求来.

IF OBJECT_ID('tempdb..#viewname') IS  NOT NULL
BEGIN
    DROP TABLE #viewname
END

SELECT 'v_'+name AS name ,id
INTO #viewname
FROM #tablename

接下来就是核心的代码,之前就说过我们可以通过id来循环获取表名来创建视图,当然也可以通过这样的方法来获取列名,只不过我们要处理一下列名,将其处理成 a,b,c,d这样的格式.

DECLARE @j INT,@i INT,@name NVARCHAR(200),@viewname NVARCHAR(200)
SET @i=1
SET @j=1
DECLARE @B INT ,@A INT,@str NVARCHAR(200),@strw NVARCHAR(200),@last NVARCHAR(200)
SET @B=1
SET @A=1
SET @str=''
WHILE  @j<=(select max(id) from (select name ,row_number() over (order by name) id from [sys].[tables])as q)
BEGIN
    
    SELECT @name = name FROM #tablename WHERE id = @i
    SELECT @viewname=name FROM #viewname WHERE id = @i
        IF OBJECT_ID('tempdb..#str') IS  NOT NULL
        BEGIN
        DROP TABLE #str
        END
        SELECT name,ROW_NUMBER() OVER(ORDER BY name) id 
        INTO #str
        FROM syscolumns WHERE id = OBJECT_ID(@name)    ----获取数据表中的列名

            WHILE @A<=(select max(id) from (select name ,row_number() over (order by name) id FROM syscolumns WHERE id = OBJECT_ID(@name))as q)
                BEGIN
                    SELECT @strw=name from #str where id=@B
                    IF @str=''
                    BEGIN
                        SET @str = '['+@strw+']'
                        --SET @str = @strw
                    END
                    ELSE
                    begin
                        SET @str = @str+','+'['+@strw+']'
                        --SET @str = @str+','+@strw
                    end
                    SET @B = @B+1
                    SET @A = @A+1
                END
            PRINT @str
    EXEC('create view '+ @viewname +' as (select  '+@str+'  from ['+@name+'])')SET @i=@i+1
    SET @j=@j+1
    SET @B=1
    SET @A=1
    SET @str=''
END