SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


view generator


view generator

Author
Message
Romain Ferraton
Romain Ferraton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 9
Comments posted to this topic are about the item view generator
Jonathan AC Roberts
Jonathan AC Roberts
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10795 Visits: 4826
Why wouldn't you just create synonyms as these view seem to be a select all columns from the tables?
Thom A
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86887 Visits: 22268
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896077 Visits: 48245
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Romain Ferraton
Romain Ferraton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 9
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.

Romain Ferraton
Romain Ferraton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 9
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.

Romain Ferraton
Romain Ferraton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 9
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
Eirikur Eiriksson
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164619 Visits: 23862
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.
Cool

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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896077 Visits: 48245
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mike1234321
mike1234321
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 8
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
romain.ferraton 82381
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 69
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
mike1234321
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 8
That makes sense. Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search