MS SQL Server - Information-schema views needing refresh.

Author

Information-schema views needing refresh.

SQLboston

2005-09-29, 8:23 pm

I use sp_refreshview [view-name] to refresh Information-schema views but it
says the object does not exist. I go to EM and I can see the views.
I am using SQL Server sp4.

Any help appreciated.

Jerry Spivey

2005-09-29, 8:23 pm

SQLBoston,

First of all - GO RED SOXS!!!

Second - does the view show up in either of these queries?:

SELECT * FROM SYSOBJECTS
WHERE XTYPE = 'V'

SELECT * FROM INFORMATION_SCHEMA.VIEWS

If you perform a refresh on views in EM does the view still appear?

Is the view owned by dbo? If not you may need to qualify the owner of the
view as well (owner.view_name)

HTH

Jerry
"SQLboston" < SQLboston@discussion
s.microsoft.com> wrote in message
news:EE92F19F-B50B-4D47-B0E3- EEDAB015770A@microso
ft.com...
>I use sp_refreshview [view-name] to refresh Information-schema views but it
> says the object does not exist. I go to EM and I can see the views.
> I am using SQL Server sp4.
>
> Any help appreciated.

SQLboston

2005-09-30, 7:23 am

Jerry,

We have a showdown with Yanks this weekend! Go Sox!

I will check your scripts today.

However, I can see the views in EM and I can see their owner.
They are owned by Information-schema. I understand these scripts are created
by master database. some of these views are Views, Columns, Tables, etc.

Thanks.
Frank

"Jerry Spivey" wrote:

> SQLBoston,
>
> First of all - GO RED SOXS!!!
>
> Second - does the view show up in either of these queries?:
>
> SELECT * FROM SYSOBJECTS
> WHERE XTYPE = 'V'
>
> SELECT * FROM INFORMATION_SCHEMA.VIEWS
>
> If you perform a refresh on views in EM does the view still appear?
>
> Is the view owned by dbo? If not you may need to qualify the owner of the
> view as well (owner.view_name)
>
> HTH
>
> Jerry
> "SQLboston" < SQLboston@discussion
s.microsoft.com> wrote in message
> news:EE92F19F-B50B-4D47-B0E3- EEDAB015770A@microso
ft.com...
>
>
>

Jerry Spivey

2005-09-30, 11:23 am

Sorry SQLBoston I missed the part that these are information_schema views.
One game back...hopefully by Monday they'll be up a game!

OK...so if you run this:

USE Master
GO
sp_refreshview & #91;INFORMATION_SCHE
MA.VIEWS]

You get this:

Server: Msg 229, Level 14, State 8, Procedure sp_refreshview, Line 1
DBCC permission denied on object 'VIEWS', database 'master', owner
'INFORMATION_SCHEMA'
.

Even if you are logged in with SYSADMIN permissions.

Well as I see it the information_schema views are provided by Microsoft as
an abstraction layer to the system tables. Neither the views nor the system
tables should ever be altered by a DBA so there really is no need to perform
a sp_refreshview on an information_schema view.

HTH

Jerry
"SQLboston" < SQLboston@discussion
s.microsoft.com> wrote in message
news:9D22B938-D7D7-4890-9DAD- F1BC2EB30FAD@microso
ft.com...[color=darkred]
> Jerry,
>
> We have a showdown with Yanks this weekend! Go Sox!
>
> I will check your scripts today.
>
> However, I can see the views in EM and I can see their owner.
> They are owned by Information-schema. I understand these scripts are
> created
> by master database. some of these views are Views, Columns, Tables, etc.
>
> Thanks.
> Frank
>
>
> "Jerry Spivey" wrote:
>

Sponsored Links