SQL Server, SSMS, and Unicode characters

Background

Here I am fraught with insomnia.

Tried to review statistics on a table, but getting an error.

Error

Cannot find a table or object with the name

Text

Msg 2501, Level 16, State 45, Line 3
Cannot find a table or object with the name “[dbo].[M?_TEST]”. Check the system catalog.

Trouble Shooting

Review Tables

Outline

Let us Identify tables that have Unicode characters in their names.

The steps we took is to look the contents of the sys.tables view.

We compare the contents of sys.tables.name with cast(sys.tables.name as varchar(255)).

SQL


select
            [src]
                =  'sys.tables'

         , tblST.[name]

         , [containsUnicodeChar]
                = case
                        when
                            (
                                tblST.[name]
                                    != cast(tblST.[name] as varchar(255))
                            ) then 1
                        else 0
                   end

from   sys.tables tblST

order by
           [containsUnicodeChar] desc
         , tblST.[name] asc

Output

metadata.20190619.0457PM

Review Primary Key Constraints

Outline

Let us Identify primary key constraints that have Unicode characters in their names.

SQL

select
          [src]
            = 'sys.key_constraints'

        ,  [constraint]
            = tblSKC.[name]

        , [parent]
            = object_schema_name(tblSKC.[parent_object_id])
                + '.'
                + object_name(tblSKC.[parent_object_id])

        , [containsUnicodeChar]
                = case
                        when
                            (
                                tblSKC.[name]
                                    != cast(tblSKC.[name] as varchar(255))
                            ) then 1
                        else 0
                   end

from   sys.key_constraints tblSKC

order by
           [containsUnicodeChar] desc

         , [parent]

         , [name]

Output

metadata.constraints.primaryKeys.20190619.0500PM.PNG

 

Summary

Occasionally, Unicode characters can sneak into SQL object definitions and data.

SSMS does not always clearly indicate Unicode characters.

Please take care.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s