August 22, 2012 at 1:37 am
I manage a SQL 2008 R2 Production environment for an application. We have a new requirement to create views( simple--select * from tablename) for all existing tables (182 tables) in the database.
We will then provide access to these views for Reporting Service users to create their reports. This is so as not to affect database performance for App users.
Can someone provide me a script which generates 'create view' scripts for all existing user tables in the database?
Thanks in advance.
August 22, 2012 at 1:49 am
You won't get any performance improvement from doing this, and will just end up with a load of objects you don't need. I advise you not to do it.
If you really insist on doing it, it's very simple. Here's a basic example:
SELECT 'blah blah blah ' + TABLE_NAME + ' blah blah blah'
FROM INFORMATION_SCHEMA.TABLES
John
August 22, 2012 at 2:18 am
Ankur Bajaj (8/22/2012)
... This is so as not to affect database performance for App users....
Absolute nonsense. If you must do it, then don't do it for this reason.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2012 at 2:30 am
Yes I know it seems silly to do this. But we have security reasons also for doing this. Besides this practice is common in data warehouse environments where reporting users are given access to only views, while processes like ETL update the tables.
Thanks
August 22, 2012 at 2:30 am
A view does not contain any data - it is just a query stored in a view object.
So, if anything, it will make performance marginally worse, not better.
There is no performance gain for any other system by using views for reporting.
See 'What is a View' here:
http://www.codeproject.com/Articles/38560/Overview-of-View-in-SQL-Server-2005">
http://www.codeproject.com/Articles/38560/Overview-of-View-in-SQL-Server-2005
Some people use a separate reporting database to achieve this effect.
There are several opinions on this issue. See this for example:
http://www.sqlservercentral.com/Forums/Topic712496-146-1.aspx">
http://www.sqlservercentral.com/Forums/Topic712496-146-1.aspx
August 22, 2012 at 2:39 am
Point Taken.
However I still need a script for creating a view corresponding to each user table in the database.
Thanks
August 22, 2012 at 3:57 am
Guys, thanks for your inputs. I have created the below script which serves my purpose for now.
Cheers!
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS SELECT * FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING'
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
August 22, 2012 at 8:51 am
Ankur Bajaj (8/22/2012)
Guys, thanks for your inputs. I have created the below script which serves my purpose for now.Cheers!
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS SELECT * FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING'
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
Keep in mind that your views are NOT dynamic. What I mean by that is just because you define the view as select * the view will NOT show any new columns and it will start to do weird things when columns are removed. If there is another column in the table it will replace the column in the view with the next column (assuming similar enough datatype). If there are no more columns it will throw an exception if a column is removed. Look closely at the below example for proof:
create table ViewTest
(
SomeID int identity primary key,
Col1 varchar(20),
Col2 varchar(20)
)
go
insert ViewTest
select 'SomeValue', 'SomeOtherValue'
go
create view vwViewTest as
select * from ViewTest
go
--These should be identical at this point
select * from ViewTest
select * from vwViewTest
go
--add a new column to the base table
alter table ViewTest
add Col3 varchar(10)
go
select * from ViewTest --this will show the new column
select * from vwViewTest --where is the new column???
go
--remove the second column from the view
alter table ViewTest
drop column Col2
go
select * from ViewTest --This will now show Col1 and Col3
select * from vwViewTest --hmmm this now shows Col1 and Col3 too >.<
go
--remove Col3, there is now only 2 columns in the table (the primary key and Col1)
alter table ViewTest
drop column Col3
go
select * from ViewTest
select * from vwViewTest --what??? This fails???
drop view vwViewTest
drop table ViewTest
The approach you are taking is going to cause you countless weeks of pain and suffering. It has been recommended by other in here not to do this. Maybe when you see how painful this is you will consider a different approach.
You CAN make this work with view but you will have to refresh your view(s) every time there is a ddl change. My advice is to not walk away from this approach. Turn around and run as though a bomb is about to go off behind you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2012 at 2:11 pm
Ankur Bajaj (8/22/2012)
Guys, thanks for your inputs. I have created the below script which serves my purpose for now.Cheers!
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS SELECT * FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING'
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
In case anyone thinks this example is a good idea I just want to add my vote to what Sean already suggested, namely: Never, never use SELECT * FROM a table in a view. Using SELECT * like that is a sure route to a whole lot of problems.
August 22, 2012 at 2:52 pm
sqlvogel (8/22/2012)
Ankur Bajaj (8/22/2012)
Guys, thanks for your inputs. I have created the below script which serves my purpose for now.Cheers!
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS SELECT * FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING'
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
In case anyone thinks this example is a good idea I just want to add my vote to what Sean already suggested, namely: Never, never use SELECT * FROM a table in a view. Using SELECT * like that is a sure route to a whole lot of problems.
+1 on this as well.
August 22, 2012 at 4:19 pm
I have to add my +1 on this. Very bad practice. Just because you can doesn't mean you should.
August 23, 2012 at 4:26 am
People having a good laugh at my cost! +1 to that.
Jokes apart, sometimes in an organization, one has to go by what his superiors think is the correct way to do things. I can argue and put my point across but at the end of the day, decisions are taken by Leads only. So i am only implementing someone else's plan.
Anyways, I have redesigned my script and used SCHEMABINDING option to avoid problems with select * and also so that 1.Views get automatically refreshed incase of DDL changes in underlying tables and 2. For creating Indexed views.
I have created a configuration table which stores the table schema and names for a database and then used a stored procedure with a cursor to auto-genearte the CREATE VIEW scripts for all tables.Anyone interested can have a look. Comments and suggestions are welcome.
----TABLE-----
select TABLE_SCHEMA,TABLE_NAME
into dbo.config_Tables
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
group by TABLE_SCHEMA,TABLE_NAME
----Stored Procedure-------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[usp_gen_view_script]
AS
DECLARE @Catalog VARCHAR(100) = 'ITOPS'---database Name
DECLARE @Columns VARCHAR(MAX) = ''
DECLARE @Schema VARCHAR(100)
DECLARE @Table VARCHAR(200)
--CURSOR
DECLARE curTables CURSOR LOCAL
FOR SELECT TABLE_SCHEMA,TABLE_NAME
FROM dbo.config_Tables
FOR READ ONLY
OPEN curTables
FETCH NEXT FROM curTables INTO @Schema,@Table
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT
@Columns = @Columns + COALESCE('[' + COLUMN_NAME + ']' + ',', '')
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG = @Catalog
AND TABLE_SCHEMA = @Schema
AND TABLE_NAME = @Table
ORDER BY
ORDINAL_POSITION
SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns) - 1)
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'SELECT ' + @Columns + ' FROM ' + @Schema + '.' + @Table
--PRINT @Sql
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' WITH SCHEMABINDING'+
' AS ' + @Sql
FROM INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = @Schema
AND TABLE_NAME = @Table
END
FETCH NEXT FROM curTables INTO @Schema,@Table
END
DEALLOCATE curTables
GO
August 23, 2012 at 4:35 am
Ankur Bajaj (8/23/2012)
People having a good laugh at my cost! +1 to that....
Nobody is laughing at you. Some of the folks who have responded on this thread may at some point in their long careers have been persuaded to action a similar illogical directive, and remember the consequences. It's not funny, it's tragic.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2012 at 4:52 am
As Chris says, nobody's laughing or joking - just trying to talk you out of what would be a bad idea.
Is the "Lead" you refer to a technical lead? If (s)he is, then you have a problem, because (s)he has made a decision that is not good from a technical view, and fails to listen to (or understand)arguments to the contrary. If (s)he is not, you still have a problem, since, technical decisions shouldn't be taken by non-technical people.
The changes you have made aren't an improvement, I'm afraid - in fact they'll make things worse. First, if you use a config table, you have to maintain that table. Far better just to select from INFORMATION_SCHEMA.TABLES as and when you need so that your list is always up to date. Second, you don't need that cursor. Just use the SELECT statement that you posted earlier on.
John
August 23, 2012 at 4:56 am
INFORMATION_SCHEMA views are perhaps not so much in vogue as they were:
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply