Chapter 1: System Procedures

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

sp_checkreswords reports the names of existing objects that are reserved words. Transact-SQL does not allow words that are part of any command syntax to be used as identifiers, unless you are using delimited identifiers. Reserved words are pieces of SQL syntax, and they have special meaning when you use them as part of a command. For example, in pre-release 10.0 SQL Server, you could have a table called work, and select data from it with this query:
```
select * from work
```
work was a new reserved word in SQL Server release 10.0, part of the command commit work. Issuing the same select statement in release 10.0 or later causes a syntax error. sp_checkreswords finds identifiers that would cause these problems.
sp_checkreswords also finds reserved words, used as identifiers, that were created using the set quoted_identifier option.
Use sp_checkreswords before or immediately after upgrading to a new release of Adaptive Server. For information on installing and running this procedure before performing the upgrade, see the installation documentation for your platform.

Run sp_checkreswords in the master database and in each user database. Also run it in model and sybsystemprocs, if you have added users or objects to those databases.
The return status indicates the number of items found.
If you supply a user name, sp_checkreswords checks for all of the objects that can be owned by a user tables, indexes, views, procedures, triggers, rules, defaults, and user-defined datatypes. It reports all identifiers that are reserved words.
If your current database is not the master database, and you do not provide a user name, sp_checkreswords checks for all of the objects above, with a separate section in the report for each user name. It also checks sysusers and syssegments for user names and segment names that are reserved words. You only need to check model and sybsystemprocs if you have added objects, users, or user-defined datatypes.
If your current database is master, and you do not provide a user name, sp_checkreswords performs all of the checks above and also checks sysdatabases, syslogins, syscharsets, sysservers, sysremotelogins, sysdevices, and syslanguages for reserved words used as the names of databases, local or remote logins, local and remote servers, character sets, and languages.

Handling reported instances of reserved words

If sp_checkreswords reports that reserved words are used as identifiers, you have two options:
- Use sp_rename, sp_renamedb, or update the system tables to change the name of the identifier.
- Use set quoted_identifier on if the reserved word is a table name, view name, or column name. If most of your applications use stored procedures, you can drop and re-create these procedures with set quoted_identifier on, and quote all identifiers. All users will be able to run the procedures, without having to use set quoted_identifier on for their session. You can use set quoted_identifier on, create views that give alternative names to tables or columns, and change your applications to reference the view instead.
  
  The following example provides alternatives for the new reserved words “key”, “level”, and “work”:
```
create view keyview
as
select lvl = "level", wrk = "work"
from "key"
```
  The syntax for the set command is:
```
set quoted_identifier on
```
If you do not either change the identifiers or use delimited identifiers, any query that uses the reserved words as identifiers reports an error, usually a syntax error. For example:
```
select level, work from key
```
```
Msg 156, Level 15, State 1:
Server ’rosie’, Line 1:
Incorrect syntax near the keyword ’level’.
```
The quoted identifier option is a SQL92 option and may not be supported by many client products that support other Adaptive Server features. For example, you cannot use bcp on tables whose names are reserved words.

Before choosing the quoted identifier option, perform a test on various objects using all the tools you will use to access Adaptive Server. Use set quoted_identifier on, create a table with a reserved word for a name and reserved words for column names. If the client product generates SQL code, it must enclose identifiers in double quotes (if they are reserved words) and character constants in single quotes.
Procedures, triggers, and views that depend on objects whose names have been changed may work after the name change, but will stop working when the query plan is recompiled. Recompilation takes place for many reasons, without notification to the user. To avoid unsuspected loss of functionality, change the names of objects in procedures, triggers, and views immediately after you change the object name.
Whether you change the object names or use delimited identifiers, you must change all stored procedures, views, triggers, and applications that include the reserved word. If you change object names, you must change identifiers; if you use delimited identifiers, you must add the set quoted_identifier option and quotation marks.
If you do not have the text of your procedures, triggers, views, rules, and defaults saved in operating system files, you can use defncopy to copy the definitions from the server to files. See defncopy in the Utility Guide.

Changing identifiers

If you change the names of the items reported by sp_checkreswords, you must change the names in all procedures, triggers, views, and applications that reference the object using the reserved word.
Dump your database before changing identifier names. After you change the identifier names, run dbcc to determine that there are no problems, and dump the database again.
If you are changing identifiers on an active production database:
- Perform the changes when the system is least busy, so that you will disrupt as few users as possible.
- Prepare carefully by finding all Open Client DB-Library™ programs, windowing applications, stored procedures, triggers, and scripts that use a particular identifier. This way, you can make the edits needed in the source code, then change the identifiers and replace the procedures and code as quickly as possible.
The procedure sp_depends can help find procedures, views, and triggers that use table and view names.

Using sp_rename to change identifiers

The system procedure sp_rename renames tables, indexes, views, procedures, triggers, rule, defaults, user-defined datatypes, and columns. Use sp_renamedb to rename databases.

Table 1-10 shows the types of identifiers that you can change with sp_rename and lists other changes that may have to be made on the server and in your application programs.

**Table 1-10: **sp_rename** and changing identifiers**
Identifier	Remember To
Table name	Drop all procedures, triggers and views that reference the table, and re-create them with the new name. Use sp_depends to find the objects that depend on the table. Change all applications or SQL source scripts that reference the table to use the new table name. Change dbcc scripts that perform table-level checks using table names.
Index name	Drop any stored procedures that create or drop the index, and re-create them with the new name. Change all applications or SQL source scripts that create or drop the index. Change dbcc scripts that perform index-level checks using index names.
View name	Drop all procedures, triggers, and views that reference the view, and re-create them with the new name. Use sp_depends to find the objects that depend on the view. Change all applications or SQL source scripts that reference the view to use the new view name.
Procedure name	Drop and re-create with the new procedure name all procedures and triggers that reference the procedure. Change all applications or SQL source scripts that execute the procedure to use the new name. If another server remotely calls the procedure, change applications on the remote server to use the new procedure name.
Trigger name	Change any SQL source scripts that create the trigger.
Rule name	Change any SQL source scripts that create the rule.
Default name	Change any SQL source scripts that create the default.
User-defined datatype name	Drop all procedures that create tables with user-defined datatypes, and re-create them with the new name. Change any applications that create tables with user-defined datatypes.
Column name	Drop all procedures, triggers and views that reference the column, and re-create them with the new column name. sp_depends cannot find column name references. The following query displays the names of procedures, triggers, and views that reference a column named “key”: select distinct sysobjects.name from sysobjects, syscomments where sysobjects.id = syscomments.id and syscomments.text like "%key%" Change all applications and SQL source scripts that reference the column by name.

The following command changes the name of the view isolation to isolated:

sp_rename "isolation", isolated

The following command changes the name of a column in the renamed view isolated:

sp_rename "isolated.key", keyname

Use sp_depends to get a list of all views, procedures, and triggers that reference a view, procedure, or table that will be renamed. To use sp_depends after renaming an object, give the new name. For example:
```
sp_depends new_name
```

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

To change user names, login names, device names, remote server names, remote server user names, segment names, and character set and language names, first determine if you can drop the object or user, then add or create it again. If you cannot do that, use the following command to allow direct updates to system tables:

sp_configure "allow updates to system tables", 1

Only a System Security Officer can set the allow updates to system tables configuration parameter.

Errors during direct updates to system tables can create severe problems in Adaptive Server. To determine whether you can drop the objects or user, then re-create them, see Table 1-11.

Table 1-13 shows possible dependencies on this set of identifiers. See this table for possible dependencies, whether you choose to upgrade by dropping and recreating objects, by using delimited identifiers, or by performing direct updates to system tables.

**Table 1-11: Alternatives to direct system tables updates when changing identifiers**
Identifier type	Suggested actions to avoid updates to system tables
User names and login names	To change the name of a user with no objects, first use sp_helprotect username in each database to record the user’s permissions. Then, drop the user from all of the databases (sp_dropuser), and drop the login (sp_droplogin). Finally, add the new login name (sp_addlogin), add the new user name to the databases (sp_adduser), and restore the user’s permissions with grant.
Device names	If this device is completely allocated, you will not need to use its name in a create database command, so you can leave the name unchanged.
Remote server names	Unless there are large numbers of remote login names from the remote server, drop the remote server (sp_dropserver) and add it with a new name (sp_addserver).
Remote server logins	Drop the remote login with sp_dropremotelogin, add it with a new name using sp_addremotelogin, and restore the user’s permission to execute procedures with grant.
Segment names	These are rarely used, once objects have been created on the segments.
Character set and language names	Languages and character sets have reserved words as identifiers only if a System Administrator has created alternative languages with sp_addlanguage. Drop the language with sp_droplanguage, and add it with a new name.

WARNING! Direct updates to system tables can be very dangerous. You can make mistakes that make it impossible for Adaptive Server to run or make it impossible to access objects in your databases. Undertake this effort when you are calm and collected, and when little or no production activity is taking place on the server. If possible, use the alternative methods described Table 1-11.

The following example shows a “safe” procedure for updating a user name, with all data modification preceded by a begin transaction command. The System Security Officer executes the following command:
```
sp_configure "allow updates to system tables", 1
```
Then you can execute the following:
```
begin transaction
update sysusers
set name = "workerbee"
where name = "work"
```
At this point, run the query, and check to be sure that the command affected only the row that you intended to change. The only identifier change that affects more than one row is changing the language name in syslogins.
- If the query affected only the correct row, use commit transaction.
- If the query affected more than one row, or the incorrect row, use rollback transaction, determine the source of the problem, and execute the command correctly.
When you are finished, the System Security Officer turns off the allow updates to system tables configuration parameter with this command:
```
sp_configure "allow updates to system tables", 0
```
WARNING! Only update system tables in a single database in each user defined transaction. Do not issue a begin transaction command and then update tables in several databases. Such actions can make recovery extremely difficult.

Table 1-12 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-12: 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-13 shows other changes that may have to be made on the server and in your application programs:

**Table 1-13: 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

You can use delimited identifiers for table names, column names, and view names. You cannot use delimited identifiers for other object names.
If you choose to use delimited identifiers, use set quoted_identifier on, and drop and re-create all the procedures, triggers, and views that use the identifier. Edit the text for those objects, enclosing the reserved words in double quotes and enclosing all character strings in single quotes.

The following example shows the changes to make to queries in order to use delimited identifiers. This example updates a table named work, with columns named key and level. Here is the pre-release 10.0 query, which encloses character literals in double quotes, and the edited version of the query for use with delimited identifiers:
```
/* pre-release 10.0 version of query */
update work set level = "novice"
    where key = "19-732"
```
```
/* 10.0 or later version of query, using 
** the quoted identifier option
*/
update "work" set "level" = 'novice'
    where "key" = '19-732'
```
All applications that use the reserved word as an identifier must be changed as follows:
- The application must set the quoted identifier option on.
- All uses of the reserved word must be enclosed in double quotes.
- All character literals used by the application while the quoted identifier option is turned on must be enclosed in single quotes. Otherwise, Adaptive Server attempts to interpret them as object names.
For example, the following query results in an error message:
```
set quoted_identifier on
```
```
select * from titles where title_id like "BU%"
```
Here is the correct query:
```
select * from titles where title_id like ’BU%’
```
Stored procedures that you create while the delimited identifiers are in effect can be run without turning on the option. (The allow updates to system tables option also works this way.) This means that you can turn on quoted identifier mode, drop a stored procedure, edit it to insert quotation marks around reserved words used as identifiers, and re-create the procedure. All users can execute the procedure without using set quoted_identifier.

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