SQL Procedure to populate or create tables from views - performance issues

I have written the below to populate/create tables (t_) from all of the corresponding views (v_) in my database. I converted a previous script from using a cursor to improve running speed. Is there anything else I can do to make this more performant.

Any help is appreciated.

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[P_populate_tables_from_views]    Script Date: 22/11/2016 16:15:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[P_populate_tables_from_views]

@dropTables BIT

AS

DECLARE @viewName NVARCHAR(100);
DECLARE @formattedTableName NVARCHAR(100);
DECLARE @dropTableSql NVARCHAR(MAX);

DECLARE @viewId int

SET @viewId = (SELECT MIN(object_id)
FROM sys.views)

WHILE @viewId IS NOT NULL
BEGIN

SET @viewName = (SELECT name FROM sys.views where object_id = @viewId)

PRINT 'Time: ' + CONVERT(varchar, SYSDATETIME(), 121)
PRINT('Processing ' + @viewName)
SET @formattedTableName = REPLACE(@viewName,'v_','t_');

IF @dropTables = 1
BEGIN
SET @dropTableSql = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '''+@formattedTableName+''')) DROP TABLE '+@formattedTableName
EXEC (@dropTableSql);
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @formattedTableName))
BEGIN
EXEC('TRUNCATE TABLE '+@formattedTableName)
EXEC('INSERT INTO '+@formattedTableName +' SELECT * FROM '+ @viewName)
END
ELSE
BEGIN
EXEC ('SELECT * INTO '+@formattedTableName+' FROM '+@viewName);
END
SET @viewId = (SELECT MIN(object_id)
FROM sys.views WHERE object_id > @viewId)
END

• EXEC() won't help SP performance, AFAIK (can't say how much in this case without a decent benchmark). Why don't you drop it? – Adriano Repetti Nov 23 '16 at 10:13
• @AdrianoRepetti Supposedly truncate table is better for performance than fully dropping tables - or so I have read. Just wondered if there was anything syntax wise that could be obviously improved. – IRGUY Nov 23 '16 at 10:21
• I had to use formatting, I mean why don't you drop EXEC, I didn't mean to use DROP (yes, TRUNCATE when applicable is much faster.) BTW I assume you do not have to worry about permissions (you may not even need to give exec permission to this procedure) – Adriano Repetti Nov 23 '16 at 10:26
• @AdrianoRepetti apologies misread that. There shouldn't be any issue with permissions. So you would recommend dropping the exec to increase speed? – IRGUY Nov 23 '16 at 10:35
• Yes, it should because (AFAIK but I'm not SQL Server expert) EXEC() won't reuse execution plans. Now, in this case I suppose that saved time is negligible then it may (or it may not) be advantageous. In this regard also take a look to sp_executesql on MSDN (simple drop-in). Side note: when possible it's always nice to give as little permissions as possible to each procedure (to avoid catastrophic error, unwanted side effects and/or security issues if, in future, you will add more parameters...) – Adriano Repetti Nov 23 '16 at 10:45