Mising table

Question

Mising table

mike.conti

SSC Rookie

Points: 34
More actions
January 28, 2014 at 7:45 am

#287742

I am trying to see what happened to a table. It is a transaction table that I think may be dropped.
This works: SELECT top 10 * FROM ACTI*****_****
it does not appear in when i run this SELECT * FROM sys.tables WHERE NAME LIKE '%trans%'
It does appear when I run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='***************'
what happened to my table.

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

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

mike.conti (1/28/2014)
I am trying to see what happened to a table. It is a transaction table that I think may be dropped.
This works: SELECT top 10 * FROM ACTI*****_****
it does not appear in when i run this SELECT * FROM sys.tables WHERE NAME LIKE '%trans%'
It does appear when I run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='***************'
what happened to my table.

You have posted 3 queries here and they all reference a different table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mike.conti SSC Rookie Points: 34 More actions · Answer 2

sorry for the confusion: there is no syntax issue

This works: SELECT top 10 * FROM ACTI**_**

it does not appear in when i run this SELECT * FROM sys.tables WHERE NAME LIKE '%ACTI%'

It does appear when I run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=' ACTI**_**’

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

mike.conti (1/28/2014)
sorry for the confusion: there is no syntax issue
This works: SELECT top 10 * FROM ACTI**_**
it does not appear in when i run this SELECT * FROM sys.tables WHERE NAME LIKE '%ACTI%'
It does appear when I run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=' ACTI**_**’

Is that seriously the name of your table? If so, your first query cannot possibly work. It would have to be wrapped in [].

I was able to create a table with that name and all 3 of your queries worked just fine.

create table [ACTI**_**]

(

col int

)

go

select top 10 * from [ACTI**_**]

SELECT * FROM sys.tables WHERE NAME LIKE '%ACTI%'

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ACTI**_**'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323755 More actions · Answer 4

if a table was dropped, you might be able to find out who/ when it was dropped by looking at the default trace.

if you need the DDL to recreate the table,or the data that was in the table as well, that'll require either scripting the table from a existing copy of the db, or a restore to a new database, and the migration form the new database to the current production db wher eit is missing.

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!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Sean Lange (1/28/2014)
mike.conti (1/28/2014)
sorry for the confusion: there is no syntax issue
This works: SELECT top 10 * FROM ACTI**_**
it does not appear in when i run this SELECT * FROM sys.tables WHERE NAME LIKE '%ACTI%'
It does appear when I run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=' ACTI**_**’
Is that seriously the name of your table? If so, your first query cannot possibly work. It would have to be wrapped in [].
I was able to create a table with that name and all 3 of your queries worked just fine.
create table [ACTI**_**]
(
col int
)
go
select top 10 * from [ACTI**_**]
SELECT * FROM sys.tables WHERE NAME LIKE '%ACTI%'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ACTI**_**'

Don't forget the leading space.

create table [ ACTI**_**]

(

col int

)

go

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass