Friday, February 24, 2012

DBCC CHECKTABLE on a View

I'm trying to run the following statement:
DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
But I get this error message:
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
So I run this statement:
SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
And get this result:
name id xtype uid info status base_schema_ver replinfo parent_obj crdate
ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
version deltrig instrig updtrig seltrig category cache
vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
(1 row(s) affected)
What is going on here? TIA!CHECKTABLE is checking physical consistency of the data. A view doesn't stor
e any data so there is
nothing to check. Run CHECKTABLE on the tables that the view is querying ins
tead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993
0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>|||Thanks! I'm taking your word for it, but that seems to be in conflict with
what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
DBCC CHECKTABLE
Checks the integrity of the data, index, text, ntext, and image pages for
the specified table or indexed view.
Syntax
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> CHECKTABLE is checking physical consistency of the data. A view doesn't
store any data so there is
> nothing to check. Run CHECKTABLE on the tables that the view is querying
instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in
message
> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
sysobjects.[vbcol=seagreen]
refdate[vbcol=seagreen]
13:19:14.993 0[vbcol=seagreen]
>|||Is the view qualified with a name other than DBO?
"Ron Hinds" wrote:

> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993
0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>
>|||No - I even tried qualifying it with dbo and still got the error.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:0309BF46-E4A8-4F84-8B12-68B767AE40B6@.microsoft.com...[vbcol=seagreen]
> Is the view qualified with a name other than DBO?
> "Ron Hinds" wrote:
>
sysobjects.[vbcol=seagreen]
refdate[vbcol=seagreen]
13:19:14.993 0[vbcol=seagreen]|||BOL is talking about indexed views.
An indexed view is not a 'normal' view and use database storage.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ron Hinds" wrote:

> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> store any data so there is
> instead.
> message
> sysobjects.
> refdate
> 13:19:14.993 0
>
>|||Note "*indexed* view". Do you have an index on that view?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> store any data so there is
> instead.
> message
> sysobjects.
> refdate
> 13:19:14.993 0
>|||I see. Thanks guys!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uA5Qo$VPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Note "*indexed* view". Do you have an index on that view?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in
message
> news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
with[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
querying[vbcol=seagreen]
crdate[vbcol=seagreen]
>

No comments:

Post a Comment