sp_checkreswords

Description

Detects and displays identifiers that are Transact-SQL reserved words. Checks server names, device names, database names, segment names, user-defined datatypes, object names, column names, user names, login names, and remote login names.

Syntax

sp_checkreswords [user_name_param]

Parameters

user_name_param

is the name of a user in the current database. If you supply user_name_param, sp_checkreswords checks only for objects that are owned by the specified user.

Examples

Example 1

Shows the results if sp_checkreswords is executed in the master database:

1> /* executed in the master database */
2> sp_checkreswords
Reserved Words Used as Database Object Names for Database master
 
Upgrade renames sysobjects.schema to sysobjects.schemacnt.
 
Owner 
 ------------------------------ 
 dbo 

 Table                          Reserved Word Column Names
 ------------------------------ ------------------------------ 
 authorization                  cascade 

 Object Type                    Reserved Word Object Names 
 ------------------------------ ------------------------------ 
 rule                           constraint
 stored procedure               check
 user table                     arith_overflow
 user table                     authorization
 
 ------------------------------------------------------------- 
 ------------------------------------------------------------- 

 Owner                          
 ------------------------------ 
 lemur                          

 Table                          Reserved Word Column Names
 ------------------------------ ------------------------------ 
 key                            close

 Table                          Reserved Word Index Names
 ------------------------------ ------------------------------ 
 key                            isolation

 Object Type                    Reserved Word Object Names
 ------------------------------ ------------------------------ 
 default                        isolation
 rule                           level
 stored procedure               mirror
 user table                     key

 Reserved Word Datatype Names 
 ------------------------------ 
 identity


 ------------------------------------------------------------- 
 ------------------------------------------------------------- 

 Database-wide Objects 
 --------------------- 


 Reserved Word User Names     
 ------------------------------ 
 at
 identity

 Reserved Word Login Names
 ------------------------------ 
 at
 identity

 Reserved Word as Database Names 
 ------------------------------ 
 work

 Reserved Word as Language Names 
 ------------------------------ 
 national

 Reserved Word as Server Names
 ------------------------------ 
 mirror
 primary

 Reserved Word ServerNetNames
 -------------------------------- 
 mirror
 primary

Example 2

Shows the results if sp_checkreswords is executed in the user database user_db:

1> /* executed in the user database, user_db */
2> sp_checkreswords
Reserved Words Used as Database Object Names for Database user_db
 
 Upgrade renames sysobjects schema to sysobjects.schemacnt.
 
 Owner
 ------------------------------ 
 tamarin

 Table                          Reserved Word Column Names
 ------------------------------ ------------------------------ 
 cursor                         current
 endtran                        current
 key                            identity
 key                            varying
 schema                         primary
 schema                         references
 schema                         role 
 schema                         some 
 schema                         user 
 schema                         work 

 Table                          Reserved Word Index Names 
 ------------------------------ ------------------------------ 
 key                            double 

 Object Type                    Reserved Word Object Names
 ------------------------------ ------------------------------ 
 default                        escape 
 rule                           fetch 
 stored procedure               foreign 
 user table                     cursor 
 user table                     key 
 user table                     schema 
 view                           endtran 


 ------------------------------------------------------------- 
 ------------------------------------------------------------- 

 Database-wide Objects 
 --------------------- 

Found no reserved words used as names for database-wide objects.

Usage


Handling reported instances of reserved words


Changing identifiers


Using sp_rename to change identifiers


Renaming databases with sp_renamedb

To change the name of a database, use sp_renamedb. The database must be in single-user mode. Drop and re-create any procedures, triggers, and views that explicitly reference the database name. For more information, see sp_renamedb.


Changing other identifiers

Table 1-10 shows the system tables and columns that you should update to change reserved words. The tables preceded by “master.dbo.” occur only in the master database. All other tables occur in master and in user databases. Be certain you are using the correct database before you attempt the update. You can check for the current database name with this command:

select db_name()
Table 1-10: System table columns to update when changing identifiers

Type of identifier

Table to update

Column name

User name

sysusers

name

Login names

master.dbo.syslogins

name

Segment names

syssegments

name

Device name

sysdevices

name

Remote server name

sysservers

srvname

Remote server network name

sysservers

srvnetname

Character set names

master.dbo.syscharsets

name

Language name

master.dbo.syslanguages

master.dbo.syslogins

name

language

Table 1-11 shows other changes that may have to be made on the server and in your application programs:

Table 1-11: Considerations when changing identifiers

Identifier

Remember to

Login name

Change the user name in each database where this person is a user.

User name

Drop, edit, and re-create all procedures, triggers, and views that use qualified (owner_name.object_name) references to objects owned by this user. Change all applications and SQL source scripts that use qualified object names to use the new user name. You do not have to drop the objects themselves; sysusers is linked to sysobjects by the column that stores the user’s ID, not the user’s name.

Device name

Change any SQL source scripts or applications that reference the device name to use the new name.

Remote server name

Change the name on the remote server. If the name that sp_checkreswords reports is the name of the local server, you must restart the server before you can issue or receive remote procedure calls.

Remote server network name

Change the server’s name in the interfaces files.

Remote server login name

Change the name on the remote server.

Segment name

Drop and re-create all procedures that create tables or indexes on the segment name. Change all applications that create objects on segments to use the new segment name.

Character set name

None.

Language name

Change both master.dbo.syslanguages and master.dbo.syslogins. The update to syslogins may involve many rows. Also, change the names of your localization files.


Using delimited identifiers

Permissions

Only a system administrator can execute sp_checkreswords.

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

See also

Commands set

System procedures sp_configure, sp_depends, sp_rename, sp_renamedb

Utilities defncopy