Chapter 1: System Procedures

sp_checknames

Description

Checks the current database for names that contain characters not in the 7-bit ASCII set.

Syntax

sp_checknames [help | silent]

Parameters

help: shows information about the system tables that are scanned.

silent

checks the current database in a silent mode, returning either:

0 – if there are no names with non-7 bit ASCII characters, or
1 – if there is at least one name witha non-7 bit ASCII character

Examples

Example 1

Checks the master database for names that contain characters not in the 7-bit ASCII set:

sp_checknames

Looking for non 7-bit ASCII characters in the system tables of database: 
"master"

===============================================================
Table.Column name: "syslogins.password"
 
The following logins have passwords that contain non 7-bit
ASCII characters.  If you wish to change them use "sp_password";
Remember, only the sa and the login itself may examine or change
the syslogins.password column:
 
 suid   name
 ------ ------------------------------ 
      1 sa
      2 probe
      3 bogususer

Example 2

Displays information about the system tables scanned:

1> sp_checknames help 
2> go

sp_checknames is used to search for non 7-bit ASCII characters 
several important columns of system tables. The following 
columns are searched:

In "master":
    sysdatabases.name
    sysdevices.name 
    syslogins.name 
    syslogins.dbname 
    syslogins.password 
    sysremotelogins.remoteusername 
    sysservers.srvname
    sysservers.srvnetname 

In all databases: 
    syscolumns.name 
    sysindexes.name 
    sysobjects.name 
    syssegments.name 
    systypes.name 
    sysusers.name 

(return status = 0) 
1>

Example 3

Suppresses the output of system table names, and displays just the return status:

1> sp_checknames silent 
2> go

(return status = 1)

Usage

sp_checknames examines the names of all objects, columns, indexes, user names, group names, and other elements in the current database for characters outside of the 7-bit ASCII set. It reports illegal names and gives instructions to make them compatible with the 7-bit ASCII set.
Run sp_checknames in every database on your server after upgrading from a SQL Server of release 4.0.x or 4.2.x, and after using a default character set that was not 7-bit ASCII.
Follow the instructions in the sp_checknames report to correct all non-ASCII names.

Permissions

Any user can execute sp_checknames.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect