Create Views on all Tables in a Database

Dear Experts;

I have a requirement to create views on all tables in a database.  Yes, I know-dumb request, but I enjoy eating and living in a house.  Would one of you assist me in creating a SQL script that would do this?  The query below that returns a list of Schema, Table, Column and Ordinal Position in the database.  And it needs to test to see if it exists and if so drops the view and then create the new view.

Many thanks,

USE HotRod;
GO

SELECT  *
FROM    
(
SELECT TOP 100 PERCENT sc.table_schema
                     , sc.table_name
                     , sc.column_name
                     , sc.ordinal_position
from information_schema.columns sc INNER JOIN
sys.tables AS t ON sc.table_name = t.name INNER JOIN
sys.columns AS c ON c.object_id = t.object_id AND c.name = sc.Column_Name
order by table_schema, table_name, ordinal_position
)t
order by TABLE_SCHEMA
wdbatesAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
try this...it worked in one of my test databases.

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER OFF;
GO

DECLARE @SQL	NVARCHAR(4000),
	@ObjectID	INT,
	@TableName	NVARCHAR(128),
	@ViewName	NVARCHAR(128);

DECLARE crsTable CURSOR FOR
SELECT object_id, name FROM sys.tables;

OPEN crsTable;

FETCH NEXT FROM crsTable INTO @ObjectID, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'ENTER CURSOR'
	PRINT @TableName
	SELECT @ViewName = 'sData.[v'+ @TableName + ']';
	PRINT @ViewName

	IF EXISTS(SELECT * FROM sys.views WHERE name = 'v' + @TableName)
	BEGIN
		PRINT 'DROP VIEW'
		SELECT @SQL = 'DROP VIEW ' + @ViewName;
		PRINT @SQL;
		EXECUTE sys.sp_executesql @SQL;
	END

	SELECT @SQL = 'CREATE VIEW ' + @ViewName + ' AS '
		+ 'SELECT ' +
		STUFF((
			SELECT ',' + name
			FROM sys.columns
			WHERE object_id = T.object_id
			ORDER BY column_id
			FOR XML PATH('')
		), 1, 1, '') + ' FROM ' + T.name
	FROM sys.tables AS T

	PRINT @SQL;

	EXEC sys.sp_executesql @SQL;

	FETCH NEXT FROM crsTable INTO @ObjectID, @TableName;
END

CLOSE crsTable;
DEALLOCATE crsTable;

SELECT * FROM sys.views

Open in new window

0
 
F PCommented:
Use IF IS NOT NULL, then after the DROP, rebuild.

USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.Reorder', 'V') IS NOT NULL
DROP VIEW dbo.Reorder ;
GO

Open in new window


https://msdn.microsoft.com/en-us/library/ms173492.aspx
0
 
wdbatesAuthor Commented:
Hello Frank;

I know how to drop and create a view, but i need a query/SP to do this for all the tables in a database.

Bill
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
F PCommented:
You mean a stored procedure which would recursively loop through the returned records and you can write the DROP against? Then all you'd have to do is EXECUTE it. I can write one if you need, just making sure.
0
 
Brian CroweDatabase AdministratorCommented:
Here is a script to create the view for a given table.  You could just wrap this in a cursor and cycle through the tables to check for the existence of the view and then if necessary create one.  You will need to change the hard-coded object_id in the example to one from your sys.tables to verify it.

DECLARE @SQL NVARCHAR(4000)


SELECT @SQL = 'CREATE VIEW dbo.[view_' + T.name + '] AS '
	+ 'SELECT ' +
	STUFF((
		SELECT ',[' + name + ']'
		FROM sys.columns
		WHERE object_id = T.object_id
		ORDER BY column_id
		FOR XML PATH('')
	), 1, 1, '') + ' FROM ' + T.name
FROM sys.tables AS T
WHERE object_id = 2815072

SELECT @SQL

EXEC sys.sp_executesql @SQL;

Open in new window

0
 
UnifiedISCommented:
This will generate both the drops and creates.  I just went with "select *", seemed reasonable if you are going to regularly drop and recreate them.

SELECT 'IF OBJECT_ID (''dbo.vw_' + TABLE_NAME + ''', ''V'') IS NOT NULL DROP VIEW dbo.vw_' + TABLE_NAME + ''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

SELECT 'CREATE VIEW ' + 'dbo.vw_' + TABLE_NAME + ' AS SELECT * FROM ' + TABLE_NAME + char(13) + 'GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
0
 
wdbatesAuthor Commented:
Hello Brian;

That's where I need the help, the looping.  I can create the list of Schema, Tables and Columns for the database, but creating the script to loop through the query is where I'm stuck.  The view's name would be something like sData.vClassOfUsers where sData is the Schema and ClassOfUsers is the table.

Bill
0
 
wdbatesAuthor Commented:
Hello Experts;

This is the format that I'm required to use:

USE [HotRod]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sDATA].[TestPassView]'))
DROP VIEW [sDATA].[TestPassView]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW [sDATA].[TestPassView] AS
    SELECT [DataValue],
           [Description]
      FROM sDATA.TestPass

GO
0
 
wdbatesAuthor Commented:
Brian;

That's the ticket.  Thank you for your rapid response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.