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.
sp_checkreswords [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.
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
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.
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.
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.
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.
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-8 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.
Identifier |
Remember To |
|---|---|
Table name |
|
Index name |
|
View name |
|
Procedure name |
|
Trigger name |
|
Rule name |
|
Default name |
|
User-defined datatype name |
|
Column 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
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.
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-9.
Table 1-11 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.
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-9.
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.
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-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()
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:
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. |
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.
Only a system administrator can execute sp_checkreswords.
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 |
|
Commands set
System procedures sp_configure, sp_depends, sp_rename, sp_renamedb
