Cannot Drop Table with Non-Printable Character on its name
-
Hi, folks.
I have a legacy table which name contains non-printable characters (CHAR(31), to be more specific).
The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+_ shoots the CHAR(31) character (which means "US" - Unit Separator). The previous developer should've hit by mistake this combination, and created the table with this weird character on it.
When we issue a SELECT command against the table, it returns results. But when we try to issue any DDL to it (DROP, sp_rename, etc), it simply doesn't work.
Examples:
DROP Table_Name;raises "Msg 15225 - No item by the name of 'Table_Name' could be found in the current database 'MyDB', given that @itemtype was input as '(null)'".
sp_rename 'Table_Name', 'NewTableName';raises "Msg 102 - Incorrect syntax near '_Name'".
I already duplicated the table with the correct name, and have corrected it on the referenced objects (SP's, Views etc). The remaining step is just dropping it from the database.
Just an insight: when we copy+paste from SQL Server to Notepad++, it shows the hidden chacacter ("US") on the middle of the table name, beside the underscore.
Any help? Thanks in advance.
- Thiago
-
Thiago
What happens if you try to use the GUI to drop the table?
John
-
Hi, John. Thanks for your reply.
Unfortunately, the table isn't shown under the GUI 🙁
Furthermore, this weird table neither shows up when I search for it in systems views (sys.tables, sys.views, sys.synonyms). It only shows up when we submit a SELECT statement.
-
can you try assigning the name to a variable and renaming it?
DECLARE @tb sysname
select @tb = name from sys.tables where name like 'Table%' and name like '%Name'
exec sp_rename @tb,'Table_Name'
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! -
Hi, Lowell. Thanks for replying.
Unfortunately, this table cannot be found on sys.tables. Given this, I wondered if it in fact was a view or a synonym, but cannot find any object macthing LIKE this name. Neither a TVF.
As a last resource, I'll forget about this strange hidden table.
-
I just tested this out... since you can copy it into NotePad++, try the DDL as:
DROP TABLE [<table name copied from NotePad++>];The key here is to quote the names with the brackets.
Edit: Arg, stupid XML formatting... Just replace everything between the "[" and "]" with the name of the table from NotePad++ (where is shows the "US" there)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
its possible it was marked as a system object; try sys.all_objects instead.
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! -
tanitelle (9/21/2015)
Hi, Lowell. Thanks for replying.Unfortunately, this table cannot be found on sys.tables. Given this, I wondered if it in fact was a view or a synonym, but cannot find any object macthing LIKE this name. Neither a TVF.
As a last resource, I'll forget about this strange hidden table.
Lowell (9/21/2015)
its possible it was marked as a system object; try sys.all_objects instead.Are you sure that you're selecting from the table with the "US" in the table name?
Have you refreshed the SSMS view? (It's only automatically refreshed if you use the GUI for dropping the table.)
I'm starting to wonder if the table has already been dropped. Can you run the following select statements against both of the tables?
SELECT TOP (5) sys.fn_PhysLocFormatter(%%physloc%%)FROM [tablename]
ORDER BY
Add to this the column(s) that make up the primary key for the ORDER BY clause. Replace "tablename" with the appropriate table name. Please post the results.
Oh... what this query does... it uses two undocumented system functions. %%physloc%% returns the physical location of the row. sys.fn_PhysLocFormatter takes this and puts it into a File:Page:Slot format. If the values are the same between the two queries, then you are getting the results from the same location.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Thank you guys, for the replies and for your time.
WayneS (9/21/2015)
I just tested this out... since you can copy it into NotePad++, try the DDL as:DROP TABLE [<table name copied from NotePad++>];The key here is to quote the names with the brackets.
Edit: Arg, stupid XML formatting... Just replace everything between the "[" and "]" with the name of the table from NotePad++ (where is shows the "US" there)
Wayne, I tried to emulate the error scenario. I've created a new table containing the unprintable character, tried to manipulate it, and got the following results:
CREATE TABLE tb_test (id INT, value VARCHAR(10)); -- Incorrect syntax near '_test'.
CREATE TABLE [tb_test] (id INT, value VARCHAR(10)); -- Command(s) completed successfully.
INSERT INTO [tb_test] VALUES(1,'bar'); -- Invalid object name 'tb_test'.
INSERT INTO tb_test VALUES(1,'bar'); -- Invalid object name 'tb_test'.
SELECT * FROM tb_test; -- Invalid object name 'tb'.
SELECT * FROM [tb_test]; -- Only way to insert records was by right clicking on GUI "Edit Top 200 Rows"
DROP TABLE tb_test; -- Incorrect syntax near '_test'.
DROP TABLE [tb_test]; -- Command(s) completed successfully.
The thing is that, unlike the previous example, when I query the real strange table (or synonym or view or whatever it is), if I use squared brackets, the query doesn't run.
The SELECT only runs if I don't use the square brackets notation.
Lowell (9/21/2015)
its possible it was marked as a system object; try sys.all_objects instead.Lowell, I just tried sys.all_objects view, without success. It's really strange that a queriable object misteriously doesn't appear on it, after all is reads ALL_objects...
WayneS (9/21/2015)
Are you sure that you're selecting from the table with the "US" in the table name?Have you refreshed the SSMS view? (It's only automatically refreshed if you use the GUI for dropping the table.)
Yes, pretty sure. I select from the table with the "US" on its name.
I manually refreshed the SSMS view. Only the "new" table (the one named without the "US") appears - I know because these table's structure are different. I removed some columns on the new (correct) table.
I just want to get rid from the old one. As I said, I can just ignore it. Once nobody knows about it, and it doesn't appear on the GUI neither on the SYS views, it will not make difference.
I even should not bother you guys (experts) with a less important question like this, which low (near zero) impact on our production system. It could even be a silly mistake from my part.
However, curiosity is compelling. 🙂
WayneS (9/21/2015)
I'm starting to wonder if the table has already been dropped. Can you run the following select statements against both of the tables?SELECT TOP (5) sys.fn_PhysLocFormatter(%%physloc%%)FROM [tablename]
ORDER BY
Add to this the column(s) that make up the primary key for the ORDER BY clause. Replace "tablename" with the appropriate table name. Please post the results.
Now I've noticed that this strange table is a full copy of another one, that have a fully different name. The number and content of rows on both tables are identical.
When I run CTRL+L (show execution plan) on the table with non-printable characters, it shows the Primary Key Clustered Index of its "mother" table.
Therefore, I can assume it is a View or at least a synonym, right?
After run the sys.fn_PhysLocFormatter function, I got the same physical address for its rows.
However, I can't locate a synonym neither a view for this object (in SSMS or sys.views or sys.synonyms). Paradoxical.
To sustain the view/synonym hipothesis, I've made an insert on the "mother" table, and it instantly reflected on the Non-printable character "table".
I went on the "View Dependencies" panel on the "mother" table on SSMS, but it does not helped much. :sick:
-
Update:
I've found that when SELECTing the table with the non-printable character, the reason why it returns results from another table, is that during parsing, SQL Server removes the part of name after the hidden character.
Translation:
... Whereas a table named "Table" also exists, and is exactly the one which results are returned.
Thus, as Wayne said, yes, the problematic table have already been dropped, and this question should be marked as closed and resolved.
Once more, what I initially thought as a bug, was a feature 😎
Thanks in advance for your time and comments.
Thiago
-
tanitelle (9/21/2015)
Update:I've found that when SELECTing the table with the non-printable character, the reason why it returns results from another table, is that during parsing, SQL Server removes the part of name after the hidden character.
Translation:
... Whereas a table named "Table" also exists, and is exactly the one which results are returned.
Thus, as Wayne said, yes, the problematic table have already been dropped, and this question should be marked as closed and resolved.
Once more, what I initially thought as a bug, was a feature 😎
Thanks in advance for your time and comments.
Thiago
At this point, I'm thinking that it's treating that "US" as whitespace, and then the "_name" was just made into a table alias.
I wonder if you could change your query to change the where clause to each of these, and see what you get:
WHERE Table.Id = 1 -- I'm thinking that this will generate an error.
WHERE _Name.Id = 1 -- I'm thinking that this will run.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
By the beard of the Prophet, it works!!!
SQL Server actually converted the "US" character (Unit Separator) into a space, thus turning half of the table's name into an alias. 😀
I've tested this query with several of this called non-printable characters (ranging from ASCII 01 to 31), and it runned with them all - inspite of the code meaning, it always replaced it with an space character.
Notably, the first printable character is exactly the Space - CHAR(32).
Best regards! This site (I mean, the contributors that make it) is a mine of gold.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply