view generator

Question

Post reply

view generator

Flashx

SSC Enthusiast

Points: 105
More actions
November 2, 2018 at 1:35 pm

#416431

Comments posted to this topic are about the item view generator

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 1

Why wouldn't you just create synonyms as these view seem to be a select all columns from the tables?

Thom A SSC Guru Points: 99133 More actions · Answer 2

That's very open to injection as well, you should be using QUOTENAME, not wrapping the object names with brackets ([]); the latter can easily be got around. I'd recommend using sysname as your parameter type as well. An object's name can contain any character, and have up to 128 characters, and sysname is a synonym for nvarchar(128). Although it would likely be "foolish" to call your table something like "My long table name! [For individual customers] {no company names allowed} ... /*More characters up to to make the name ever longer?!*/" it could very well exist.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 3

While I appreciate some of the technical aspects of the script, silently dropping existing views scares the heck out of me. I'd recommend adding a parameter that would default to producing a list of views that were going to be dropped without dropping them so that the DBAs and Developers don't come after me with blood in their eyes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Flashx SSC Enthusiast Points: 105 More actions · Answer 4

Jonathan AC Roberts - Saturday, November 3, 2018 8:25 AM
Why wouldn't you just create synonyms as these view seem to be a select all columns from the tables?

It is also a possibility for sure, you're right but sometimes you could find a views layer usefull because you can put filters on views. Even if it is only on some views, you have always the same object type (views) as an output layer.

Flashx SSC Enthusiast Points: 105 More actions · Answer 5

Thom A - Saturday, November 3, 2018 8:51 AM
That's very open to injection as well, you should be using QUOTENAME, not wrapping the object names with brackets ([]); the latter can easily be got around. I'd recommend using sysname as your parameter type as well. An object's name can contain any character, and have up to 128 characters, and sysname is a synonym for nvarchar(128). Although it would likely be "foolish" to call your table something like "My long table name! [For individual customers] {no company names allowed} ... /*More characters up to to make the name ever longer?!*/" it could very well exist.

Thank for the advices, You're 100% right, the new version (pushed today) will correct this.

Flashx SSC Enthusiast Points: 105 More actions · Answer 6

Jeff Moden - Saturday, November 3, 2018 12:04 PM
While I appreciate some of the technical aspects of the script, silently dropping existing views scares the heck out of me. I'd recommend adding a parameter that would default to producing a list of views that were going to be dropped without dropping them so that the DBAs and Developers don't come after me with blood in their eyes.

I added a @Execute parameter (by default to True) that will permit to chose to really drop/create views or just produce scripts.

Eirikur Eiriksson SSC Guru Points: 182917 More actions · Answer 7

I am all for generating code and the idea of exposing tables as views in a separate schema is a valid one. The problem here is that this code would never pass my code review, far too many issues there. Principally, one should separate the code generation and the execution of the code, that way, this can easily be written as an inline table valued function.
😎

Further, there are no comments notifying that the view is generated by code and changes made will potentially be lost.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 8

Flashx - Saturday, November 3, 2018 3:53 PM
Jeff Moden - Saturday, November 3, 2018 12:04 PM
While I appreciate some of the technical aspects of the script, silently dropping existing views scares the heck out of me. I'd recommend adding a parameter that would default to producing a list of views that were going to be dropped without dropping them so that the DBAs and Developers don't come after me with blood in their eyes.
I added a @Execute parameter (by default to True) that will permit to chose to really drop/create views or just produce scripts.

Excellent. Thanks for helping folks out. In that same vein, though, I'd default it to "False" (as in to NOT execute by default) to force people to make the choice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mike1234321 SSC Enthusiast Points: 107 More actions · Answer 9

You end up with the view and underlying tables being named the same, which gives you the following error:

Msg 3705, Level 16, State 1, Line 4
Cannot use DROP VIEW with 'dbo.underlying_table' because 'dbo.underlying_table' is a table. Use DROP TABLE.

Prefacing the view name with 'v_' or something like that would fix the problem.

romain.ferraton 82381 SSC Enthusiast Points: 140 More actions · Answer 10

mike1234321 - Thursday, November 8, 2018 1:31 PM
You end up with the view and underlying tables being named the same, which gives you the following error:
Msg 3705, Level 16, State 1, Line 4
Cannot use DROP VIEW with 'dbo.underlying_table' because 'dbo.underlying_table' is a table. Use DROP TABLE.
Prefacing the view name with 'v_' or something like that would fix the problem.

The generator is built to generate views with the same name as tables but in another schema (that must exists)

mike1234321 SSC Enthusiast Points: 107 More actions · Answer 11

mike1234321

SSC Enthusiast

Points: 107

November 8, 2018 at 2:30 pm

#2012909

That makes sense. Thanks.