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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wdbatesAuthor Commented:
Brian;

That's the ticket.  Thank you for your rapid response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.