getting where clause for views
-
1 - Still not only 24/7... maybe I forget to close a few browser windows from time to time
.2 - you said it yourslef
3 - not modest.. that's just the case
. -
but wudnt this be specific for columns , wat we need is to query a view defintion and extract only those where clauses that are used for a particular table???
-
what's the hitch in having your view only select "WHERE Products.Discontinued = 0" and then querying it for either productID or categoryID....
**ASCII stupid question, get a stupid ANSI !!!** -
I take that back...I think you should go with Remi's suggestion of stored procedure - check for @productID - if > 0 then your select will be "products.discontinued > 0 and productsID > @productsID" else your select will be where "categoriesID > @categoriesID"
**ASCII stupid question, get a stupid ANSI !!!** -
thats coz view is already created now we need to query the table based on where in view for that particular table
-
Why do you HAVE TO use the view?
Won't the proc satisfy your needs?
-
So, Amrita... it sounds like what you really want is to find a way to query the META definition of the view itself to discover what WHERE clause VALUES for COLUMNS in a TABLE on which the VIEW depends? Is that clear as mud?
--SJT
-
perfect Journeyman , this is wat we need , is it possible???
-
Amrita - that's as good as altering the view...you may as well create a procedure to do this!
Also, since you don't have categoryId in your view and only the categoryname, you're not going to be able to pass that in as a parameter anyway - you can only pass in productID!
**ASCII stupid question, get a stupid ANSI !!!** -
While technically possible, I don't believe it would be easy... it would involve twisting through sysdepends, sysobjects, and syscomments at the very least. This leads me to ask a couple questions:
1. A repeat of Remi's question: Why not use a sproc to dynamically determine your WHERE clause at need and do away with the need to figure out what the view is doing?
2. For the Gurus: Is there a tool already out there that might fulfill Amrita's needs?
--SJT
-
I started one.. but nothing I can share (incomplete/non commercial). Every commercial tool I know doesn't have that level of precision on dependencies (they all use sysdepends which is innacurate).
-
hi
The reason we need is that we are building a query builder kind of utility that wud query the view definition (which is already present in databases) and then wud query the table with teh where clause as defined in the table .
so is it possible , we are using .Net so in case there is anything in .Net that can help , that wud be great or any query or basically anything to make it work
amrita
-
What kind of power user will be using this??
Wouldn't it be simpler just to give them a crash course in sql?
-
Amrita,
To answer your question, it is possible to write something.
You can get the syntax of the view writting a simple query on SysComments. You can even search for a particular table name in this syntax and get all views that contain a particular table, etc.
However, you will get the entire syntax for the view. The Where clause is not stored anywhere separately. So, you will have to write your own parse routine. This can never be perfect.
I would say it is not advisable, and avoid giving this feature in our utility if possible.
My 2 cents.
-
Use <database to query>
GO
Select * from INFORMATION_SCHEMA.VIEWS where
patindex('%<table to identify>%', view_definition) > 0
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply