conflicts with view names

Question

Post reply

conflicts with view names

Ged Aitchison

Grasshopper

Points: 24
More actions
October 22, 2006 at 9:43 pm

#91881

Hi, the issue is that we have a number of views where their view name does not match the name of the actual view. For example the view name in Enterprise Manager is MYTEST_tvMARA_MATKL_IsNullOrBlankSel. However the actual view is as follows.

CREATE VIEW dbo.[TEST tvMARA_MATKL_IsNullOrBlankSel]

AS

SELECT     ZSource, MATKL, MATNR, MTART

FROM         dbo.ttMARA

WHERE     (MATKL = N'') OR

                      (MATKL IS NULL)

As you can see, the names do not match. Apparently there a heaps just like this. Please don't ask why, I've been assigned the problem of fixing it. Now I know that I can simply re-save the view and it will do this automatically.

But before that, I would like to know a way that you could find ALL the view that have different names and also if there is a short-cut way to enable me to re-synchronise the name with the actual view?

Thanks,

Ged

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Hi..

What does Query Analzer say about the view names?

What does select * from information_schema.views return?

What tool did you use to give you that code? Scripting from Enterprise Manager, Query Analyzer or something else?

I don't often use Enterprise Manager for view & stored proc creation - only use it for table maintenance via diagrams so I'm not sure if what you're describing is possible (spose it must be) and what might cause it. Is there a way to change the way the views are listed in Enterprise Manager? For example, Windows explorer lets you view things as icons (yuk) or as details (nicer). Perhaps, if you can change the way things are listed, the method of listing is confusing and might be including the view's owner (although your script says dbo).

With answers to the above questions I, or someone else, may be better able to help.

Cheers,

Ian

Lowell SSC Guru Points: 323482 More actions · Answer 2

I've seen this before; it is caused by editing the name directly in Enterprise manager, and not via drop create/alter statements;

to prove it:

go to enterprise manager and find any view;

click the name of the view, and change it's name to "bob"

now double click the "bob" view, and you will see the original statement that exists in syscomments...CREATE VIEW VW_WHATEVER AS ....

note how it does not say CREATE VIEW BOB AS....

this can happen to all compiled objects: procs, functions and views.

result: don't use Enterprise manager to rename objects. drop and recreate them, or use sp_rename

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MattieNH SSCertifiable Points: 7463 More actions · Answer 3

Ged,

If you run the command

select * from information_schema.views

as Ian suggested, you'll see the table_name value is what the view was renamed to using Enterprise Manager, and the view_definition starts out CREATE VIEW owner.viewname before it was changed in Enterprise Manager. Someone way more skilled in string manipulation than I am could probably write a routine that compared the two viewname values and returned only the ones that didn't match.

Mattie

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

If the naming differences are that bad, generate scripts for all the views from EnterPrise Manger, drop all the views, recreate the views using the script.

--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)
Intro to Tally Tables and Functions

Ged Aitchison Grasshopper Points: 24 More actions · Answer 5

Thanks all.

As it turned out, all they wanted was to generate the knowledge of what views/stored procs are out of sync.

so I wrote a nice simple little script as per below to interrogate the database for this.

select table_catalog, table_name, view_definition from INFORMATION_SCHEMA.views

where view_definition not like '%'+table_name+'%'

This does it quite nicely.

Lowell SSC Guru Points: 323482 More actions · Answer 6

very handy Ged;

I added another selection below that you can use if you have stored procs or functions that have been renamed the same way; Thank you for writing something for the detection of this issue:

select specific_name, routine_name, routine_definition from INFORMATION_SCHEMA.routines

where routine_definition not like '%'+routine_name+'%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!