USE master
GO
IF OBJECT_ID('sp_ViewScript') IS NULL EXEC ('CREATE PROCEDURE sp_ViewScript AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_ViewScript
@Table SYSNAME = NULL,
@Quoted BIT = 1,
@CreateDrop BIT = 1,
@SortByColumnName BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @OBJECT_ID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@Table)),
@SQL VARCHAR(MAX)
IF @OBJECT_ID IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_ViewScript',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
SELECT @SQL = 'IF OBJECT_ID(''' + ViewName + ''') IS NOT NULL DROP VIEW ' + ViewName + '
GO
CREATE VIEW ' + ViewName + '
AS
SELECT
' + Util.dbo.StringConcat('t.' + CASE WHEN @Quoted = 1 THEN '[' + ColumnName + ']'
ELSE ColumnName
END, ',
') + '
FROM ' + TableName + ' t
GO'
FROM (SELECT TOP 999999
CASE WHEN s.name LIKE 't%' THEN SUBSTRING(s.name, 2, 128)
ELSE s.name
END + '.v' + t.name AS ViewName,
s.name + '.' + t.name AS TableName,
c.name AS ColumnName
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
WHERE t.is_ms_shipped = 0
AND t.OBJECT_ID = @oBJECT_ID
ORDER BY CASE WHEN @SortByColumnName = 1 THEN c.name
ELSE ''
END,
c.column_id) k
GROUP BY ViewName,
TableName
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject sp_ViewScript
GO