tables that were deleted still appear when query

Question

tables that were deleted still appear when query

Tara-1044200

SSCoach

Points: 15951
More actions
October 5, 2010 at 7:33 am

#241220

I am using the following query to get list of tables and columns but it is showing tables that were deleted.
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

doesn't sound right, as that would require a corrupted database...

lets go back to basics...you dropped some tables...are you sure it was THAT database, and not another database that they were dropped from?

could the transaction that dropped the tables have been rolled back inadvertently? maybe with xact_abort on and a transaction?

what specific tables were dropped? could they have commonly named tables in another schema? ie dbo.tablename was dropped, but prod.tablename still exists?

maybe you used a cursor to try and drop stuff dynamically, but there was a syntax error, or it never actually EXEC the string to drop the table?

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!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

ok let me expalin this way..

I used the above query to bring up the list of tables and columns in a database but it shows tables that do not exist in the database, so i am assuming that those tables were deleted before but still showup, amy be sys tables are not updated? any idea?

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 3

Is it possible the tables are not in the table list, because they are system tables?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

It's more likely that it exists but YOUR login does not have access to it so you can't query it or see it in the tables lists.

I've never ever encountered that bug and I've worked on Sql 7,8,9,10 and access 97,2000,2003,2007.

If the server says it's there.... it's there!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

I am sysadmin on that box and could not see those tables from designeer mode or when i do other quries like "SELECT * FROM sys.Tables" but i could see those tables only from this query.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Ninja's_RGR'us

SSC Guru

Points: 294069

October 5, 2010 at 8:21 am

#1231735

Have you checked if they are system tables?

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 7

What are the table names that are showing up that you cannot find?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 8

They are not systables and those table names are some like "queue_messages_2009057645"

Tara-1044200 SSCoach Points: 15951 More actions · Answer 9

some clue here , i found that xtype value for these tables is "IT" not sure what that is?

Lowell SSC Guru Points: 323518 More actions · Answer 10

g33kspeak (10/5/2010)
What are the table names that are showing up that you cannot find?

select * from sys.objects where name like 'queue_messages%'

select * from sys.tables where name like 'queue_messages%'

they are tables, type of INTERNAL_TABLE, which have the flag is_msshipped=1, so they do not appear in sys.tables.

they really are sys tables

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!

Derrick Smith SSChampion Points: 10833 More actions · Answer 11

http://msdn.microsoft.com/en-us/library/ms177596.aspx