October 30, 2018 at 5:31 am
Why would there be some orphaned table entries in sys.tables, when those tables don't exist in the main database?
How can I safely remove them from sys.tables?
Thanks,
Vinay
October 31, 2018 at 2:43 pm
datsun - Tuesday, October 30, 2018 5:31 AMWhy would there be some orphaned table entries in sys.tables, when those tables don't exist in the main database?
How can I safely remove them from sys.tables?Thanks,
Vinay
That is odd. Did you double check the database you are in when you query sys.tables?
I'd probably run DBCC CHECKDB if I saw something like that.
Sue
October 31, 2018 at 3:10 pm
datsun - Tuesday, October 30, 2018 5:31 AMWhy would there be some orphaned table entries in sys.tables, when those tables don't exist in the main database?
How can I safely remove them from sys.tables?Thanks,
Vinay
You can't modify sys.tables.
October 31, 2018 at 3:47 pm
datsun - Tuesday, October 30, 2018 5:31 AMWhy would there be some orphaned table entries in sys.tables, when those tables don't exist in the main database?
There shouldn't be. What are a couple of the names?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2018 at 11:54 am
Make sure you're also looking at the schema name along with the table name. Maybe the schema is not "dbo" and so it looks like the table's not there when it really is?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 2, 2018 at 6:27 am
Scott.
Spot on. That was the issue. The missing table has a different schema_id other than the default "1" for dbo.
My script is not filtering on "schema_id".
Many thanks for the clue.
November 2, 2018 at 11:53 am
You're welcome. Glad it helped, because that would be a confusing error indeed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply