Title SQL Server Book: Stored Procedure and Its Create BSS SQL Server Book of AppleBbs: Stored Procedure and Its Create Overwheel Storage Procedure Make the management of the database, and display about database and its user information many. The stored procedure is a precompiled collection of SQL statements and optional control flow statements to store them and processed as a unit. The stored procedure is stored in the database, which can be performed by an application through a call, and allows the user to declare variables, conditional execution, and other powerful programming functions.
The stored procedure can include program flow, logic, and query on the database. They accept parameters, output parameters, return a single or more result sets, and return values.
You can use the stored procedure for any purpose using the SQL statement, it has the following advantages:
A series of SQL statements can be performed during a single stored procedure.
Other stored procedures can be referenced from their own stored procedures, which simplifies a series of complex statements.
The stored procedure is compiled on the server at the time of creation, so it is made faster than a single SQL statement. The function of the stored procedure depends on the functions provided by the database.
Creating a stored procedure You can create a stored procedure using Transact-SQL statement Create Procedure. Consider the following items before creating a stored procedure:
You cannot combine the CREATE Procedure statement to a single batch with other SQL statements.
Creating a stored procedure The default belongs to the database owner, which allows this permission to other users.
The stored procedure is a database object, and its name must comply with identifier rules.
You can only create stored procedures in the current database. When you create a stored procedure, you should specify:
All input parameters and output parameters returned to the callout process or batch.
Perform a database operation (including calling other procedures).
Returns to the status value of the calling process or batch to indicate success or failure (and the cause of failure). Many of the management activities in Microsoft? SQL Server? 2000 are performed by a special process called a system stored procedure. The system stored procedure is created and stored in the master database with a SP_ prefix. The system stored procedure can be performed from any database without using the Master database name to fully qualify the name of the stored procedure.
It is highly recommended that you do not create a stored procedure with sp_ as a prefix. SQL Server always finds the stored procedure starting with SP_ in order:
Find the stored procedure in the Master database.
Find this stored procedure based on any qualifier (database name or owner) provided.
If the owner is not specified, use DBO as the owner to find the stored procedure. Therefore, although there may be a stored procedure created with a SP_ prefix in the current database, you will always check the master database first (even if the stored process is limited).
Important If the stored procedure created by the user is the same name as the system stored procedure, the stored procedure created will never perform the user.
If a different identification number is given a process, you can create the process with the same name as the existing stored procedure, which allows these processes to logically group. The group's grouping process can be deleted simultaneously. The process used in the same application is generally packet in this manner. For example, the process used for MY_APP applications may be named MY_PROC; 1, MY_PROC; 2, etc. Delete MY_PROC to delete the entire group. After the process is packet, you cannot delete a single process within the group.
Interim stored procedures and global temporary stored procedures are similar to the temporary table, and can be created with the method of adding a ## prefix to the process name. # Represent a local temporary stored procedure, ## represents a global temporary stored procedure. These processes will no longer exist after SQL Server is turned off.
The temporary stored procedure is useful when connecting to the earlier version of SQL Server, which does not support the use of Transact-SQL statements or batch execution plans again. Applications connected to SQL Server 2000 should use the sp_executesql system stored procedures without using a temporary stored procedure. For more information, see the cache and reuse of the program. This process is automatically deleted when the connection to create a local temporary process can perform the process.
Any connection can perform a global temporary stored procedure. Only the connection used by the user created the process is turned off, and the current execution version of the process used in all other connections is running, the global temporary stored procedure no longer exists. Once the connection used to create the process is closed, the execution of the global temporary stored procedure will no longer be allowed. Only those connectors that have been started to perform this stored procedure are run.
If you create a stored procedure without a # or ## prefix directly in the Tempdb database, you will automatically delete the stored procedure when the tempdb is recreated each time the SQL Server is turned off. The process created directly in TempDB will exist even after the connection is created. Like any other object, you can grant, reject, and abolish permissions to perform the temporary stored procedure.
CREATE Procedure Creating a stored procedure, the stored procedure is a collection of Transact-SQL statements that are saved and returned to the parameters provided by the user.
You can create a process for permanent use, or temporarily use in a session (local temporary process), or temporarily use in all sessions (global temporary process).
You can also create a stored procedure that is automatically run when you Microsoft? SQL Server? Starts.
Syntax Create Proc [edure] procedure_name [; number] [{@Parameter Data_Type} [varying] [= default] [OUTPUT]] [, ... n]
[With {recompile | Encryption | Recompile, Encryption}]
[For replication]
As SQL_Statement [... n]
Parameter procedure_name
The name of the new store process. The process name must meet the identifier rules and must be unique to the database and owners. For more information, see Using the Identifier.
To create a local temporary process, you can add a number (#procedure_name) in front of Procedure_name, to create a global temporary process, can add two numbered characters (## procedure_name) in front of Procedure_name. The full name (including # or ##) cannot exceed 128 characters. The name of the specified process owner is optional.
Number
It is an optional integer that is used to group the process of the same name to remove the same group of procedures with a Drop Procedure statement. For example, the process used by the application named ORDERS can be named ORDERPROC; 1, ORDERPROC; 2, etc. The Drop Procedure ORDERPROC statement will remove the entire group. If the name is included in the name, the number should not be included in the identifier, and should only use the appropriate delimiter before and after procedure_name.
@Parameter
Parameters in the process. One or more parameters can be declared in the CREATE Procedure statement. The user must provide the value of each declared parameter (unless defined the default value of the parameter) is defined. The stored procedure can have a maximum of 2.100 parameters. Use the @ symbol as the first character to specify the parameter name. The parameter name must meet the rules of the identifier. The parameters of each process are only used for the process itself; the same parameter name can be used in other processes. By default, the parameters can only replace constants, and cannot be used instead of the name, column name, or other database object. See Execute for more information.
DATA_TYPE
The data type of the parameters. All data types (including Text, NTEXT, and Image) can be used as a parameter of the stored procedure. However, the CURSOR data type can only be used for Output parameters. If the specified data type is CURSOR, you must also specify the varying and output keywords. For more information on the data types provided by SQL Server, please refer to the data type.
Note There is no maximum number of restrictions on output parameters that can be a Cursor data type.
Varying
Specifies the result set as the output parameter (dynamically constructed by the stored procedure, the content can be changed). Only for cursor parameters.
DEFAULT
The default value of the parameters. If default is defined, you don't have to specify the value of the parameter to execute. The default must be a constant or NULL. If the process will use the LIKE keyword to this parameter, the default value can contain wildcards (%, _, [], and [^]).
OUTPUT
Indicates that the parameters are returned to the parameters. The value of this option can be returned to exec [ute]. Use the OUTPUT parameter to return the information to the calling process. Text, NTEXT, and Image parameters can be used as the Output parameter. The output parameters using the Output keyword can be a cursor placeholder.
n
Indicates that you can specify a placeholder of 2.100 parameters.
{Recompile | Encryption | Recompile, Encryption}
Recompile indicates that SQL Server does not caches the process of the process, which will be recompiled at runtime. Use the Recompile option when using an atypical or temporary value without wreathiping an execution plan in the memory.
Encryption indicates an entry that contains Create Procedure statement text in the SQL Server encrypted SysComments table. Using Encryption prevents a process of copying the process as a SQL Server replication.
Description In the upgrade process, SQL Server uses encryption comments stored in SysComments using encryption comments in syscomments.
For replication
Specifies that you cannot perform a stored procedure created for copying on the subscriber. . The stored procedures created using the for Replication option can be used as a stored procedure filtering and can only be executed during replication. This option cannot be used with the With Recompile option.
AS
Specifies the operation to be executed.
SQL_STATEMENT
The transact-sql statement of any number and type to include during the process. But there are some restrictions.
n
It is to indicate that this process can contain a placeholder for multiple Transact-SQL statements.
Note The maximum size of the stored procedure is 128 MB.
User-defined stored procedures can only be created in the current database (except for the temporary process, the temporary process is always created in the TEMPDB). In a single batch, the CREATE Procedure statement cannot be used in combination with other Transact-SQL statements.
By default, the parameters can be empty. If the NULL parameter value is passed and this parameter is used in the Create or ALTER TABLE statement, and the column referenced in this statement does not allow NULL, SQL Server generates an error message. To prevent the NULL parameter values that are not allowed to use NULL, programming logic should be added to the process or use the default value for the column (using the Create or ALTER TABLE DEFAULT keyword). It is recommended to explicit NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in the stored procedure, such as when a temporary table is created. ANSI_DFLT_ON and ANSI_DFLT_OFF options Control SQL Server as a column avatars (if there is no specified in the CREATE TABLE or ALTER TABLE statement). If a connection execution of a connection is different from the settings of these options with the connection to create the process, the list columns created for the second connection may have different air and exhibit different behavioral ways. If NULL or NOT NULL is declared for each column, you will create a temporary table for emptying for all connections to execute the stored procedure.
When you create or change the stored procedure, SQL Server saves settings for Set Quoted_Identifier and SET ANSI_NULLS. These raw settings will be used when performing a stored procedure. Therefore, all client sessions will be ignored when the stored procedure is executed. The set quoted_identifier and set ANSI_NULLS statements that occur during the stored procedure do not affect the function of the stored procedure.
Other SET options (such as SET ARITHABORT, SETDIARNINGS, or SET ANSI_PADDings) are not saved when creating or changing stored procedures. If the logic of the stored procedure depends on a specific setting, a set statement should be added at the beginning of the process to ensure that the setting is correct. When performing a set statement from a stored procedure, this setting is only valid before the stored procedure is completed. Thereafter, the setting will return to the value when the stored procedure is called. This allows individual clients to set the desired options without affecting the logic of the stored procedure.
Note SQL Server is interpreted by an empty string as a single space or interprets the true empty string, controlled by a compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets the empty string as a single space. If the compatibility level is equal to 70, SQL Server interprets the empty string as an empty string. For more information, see sp_dbcmptlevel.
Getting information about the stored procedure To display text to create a process, execute sp_helptext in the database where the process is located, and use the process name as the parameters.
Note The stored procedures created using the Encryption option cannot be viewed using sp_helptext.
To display a report for the object reference to the process reference, use sp_depends.
To rename a process, use sp_rename.
Reference Object SQL Server allows the created stored procedure references that do not exist. Only syntax check is performed when you created. When executed, if there is no valid plan in the cache, the stored procedure is compiled to generate an execution plan. Only all objects referenced during the stored procedure are parsed during the compilation process. Therefore, if the correct stored procedure is referenced by the syntax, it can be successfully created, but it will fail during runtime because the object referenced does not exist. For more information, see Delayed Name Resolution and Compilation.
Delayed Name Resolution and Compatibility Level SQL Server allows the Transact-SQL stored procedure to reference non-existing tables when creating. This ability is called a delay name resolution. However, if the Transact-SQL stored procedure references the tables defined during the store, the compatibility level setting (settings to be set by executing sp_dbcmptlevel) is 65, the warning message is issued when creating. If the table referenced at runtime does not exist, the error message will be returned. For more information, see SP_DBCMPTLEVEL and Delay Name Resolution and Compilation. After performing the stored procedure, the process name will be stored in the Sysobjects system table, while the text of the CREATE Procedure statement is stored in syscomments. When executed for the first time, the process will be compiled to determine the best access plan for retrieving data.
The parameter stored procedure for using the CURSOR data type can only use the Cursor data type for the Output parameter. If you specify a Cursor data type for a parameter, you must also specify a Varying and Output parameters. If you specify a Varying keyword for a parameter, the data type must be CURSOR and must specify the Output keyword.
Note The CURSOR data type cannot be bind to application variables through a database API (eg OLE DB, ODBC, ADO, and DB-library). Because the Output parameter must be bound first, the application can perform a stored procedure, so the stored procedure with the CURSOR OUTPUT parameter cannot be called through the database API. These processes can only be called through Transact-SQL batch, stored procedures, or triggers to call the CURSOR OUTPUT variable to the Transact-SQL partial Cursor variable.
The following rules apply to Cursor output parameters when the CURSOR output parameter is executed:
For only the cursor, the rows returned in the result of the cursor are just rows that are in or exceed the cursor position at the end of the stored procedure, such as the 100-row result set of the RS in the process opens a non-rolling cursor.
The process extracts the result of the head 5 line of the RS.
The process returns to its caller.
Returning to the result set RS consists of the sixth to 100 lines of RS, and the cursor in the caller is in the first line of RS. For only the cursor, if the stored procedure is completed, the cursor is located in front of the first line, and the entire result set will return to the call batch, stored procedure, or trigger. When returned, the cursor will be in front of the first line.
For only the cursor, if the stored procedure is completed, the position of the cursor exceeds the end of the last row, then returns the empty result set for calling batch, stored procedures, or trigger.
The empty result set is different from the null value.
For scrolling cursors, all rows in the result set are returned to call batch, stored procedures, or flip-flops when the stored procedure is executed. When returns, the cursor remains in the process of performing the location of the extraction during the process.
For any type of cursor, if the cursor is turned off, the null value is transferred to batch, stored procedures, or triggers. If you assign a marker to a parameter, this cursor has never been opened, and this will happen.
Note The shutdown state has an impact only when it is returned. For example, the cursor can be closed during the process, then open the cursor, then return the result set of the cursor to the call batch, stored procedure, or trigger.
Temporary stored procedure SQL Server supports two temporary processes: local temporary process and global temporary process. The local temporary process can only be used by the connection to create the process. The global temporary process can be used by all connections. The local temporary process is automatically removed at the end of the current session. The global temporary process is removed at the end of the last session using the process. Usually at the end of the session of the process.
Temporary process with # and ## naming, can be created by any user. After the creation process, the owner of the partial process is the only user who can use the process. Permissions to execute local temporary processes cannot grant other users. If a global temporary process is created, all users can access the process and permissions cannot be explicitly abolished. Only users with explicit CREATE Procedure permissions in the Tempdb database can also be explicitly created in the database (not named naming). You can grant or abolish the permissions during these processes. Note Frequent use of temporary stored procedures to create contentions on system tables in TEMPDB, resulting in negative impact on performance. SP_EXECUTESQL is recommended instead. Sp_executesql is not stored in the system table, so this problem can be avoided.
Automatically perform a stored procedure SQL Server starts one or more stored procedures. These stored procedures must be created by the system administrator and executed as a background process under the SYSADMIN fixed server role. These processes cannot have any input parameters.
There is no restriction on the number of startup processes, but pay attention to each startup process takes up a connection. If multiple processes must be performed at startup, do not need parallel execution, you can specify a process as a startup process, allowing the process to call other processes. This takes only one connection.
After the last database is restored at startup, the stored procedure is started. To skip the execution of these stored procedures, specify the startup parameters as the tracking mark 4022. If SQL Server is started at a minimum configuration, the stored procedure will not be executed. For more information, see Tracking Tags.
To create a startup store, you must log in as a member of the sysadmin fixed server role and create a stored procedure in the Master database.
Using sp_procoption:
Specify an existing stored procedure as a startup process.
Stop execution of the SQL Server startup.
View the list of all procedures performed when SQL Server starts. The stored procedure nested storage procedure can nested, that is, a stored procedure can call another stored procedure. When executed by the calling process, the nested level will increase, and after the end of the called process is completed, the nested level will be reduced. If the largest nested level is exceeded, the entire calling process chain failed. The @@ NestleVel function can be used to return the current nested level.
To estimate the compiled stored procedure size, use the following Performance Monitoring Counters.
Performance Monitor Object Name Performance Monitor Name SQLServer: Buffer Manager Cache Size (Page Number) SQLServer: Cache Manager Cache His Motion Cache Page Cache Object Count *
* Various categories can use these counters, including special SQL, prepared SQL, process, trigger, and the like.
For more information, see SQL Server: Buffer Manager objects and SQL Server: Cache Manager objects.
SQL_STATEMENT limits except for SET Showplan_Text and SET Showplan_all (these two statements must be only a statement in batch), any SET statement can be specified inside the stored procedure. The selected set option is valid during the execution of the stored procedure and then restores the original setting.
If other users want to use a stored procedure, the object names used by some statements must be limited by the object name of the object owner within the stored procedure. These statements include:
Alter Table
Create Index
Create Table
All DBCC statements
Drop table
Drop Index
Truncate Table
Update Statistics Permissions CREATE Procedure Permissions The default grants Sysadmin fixed server role members and DB_OWNER and DB_DDLADMIN fixed database role members. SYSADMIN Fixed Server Role Members and DB_OWNER Fixed Database Roles Members You can transfer Create Procedure permissions to other users. The owner of the permissions to execute the stored procedure, the owner can set the execution permission for other database users. Example A. Use the stored procedures under the four tables of the four tables to return all authors (provided names), published by the stored procedures. This stored procedure does not use any parameters.
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN Titles t on t.title_id = ta.title_id inner join public = p.pub_idgo
AU_INFO_ALL stored procedures can be performed by the following methods:
Execute au_info_all-- orexec au_info_all
If the process is the first statement in the batch, you can use:
AU_INFO_ALL
B. Use the stored procedures under the simple process of parameters to return only the designated author (provided name), published books, and publishing agencies. This stored procedure accepts a value that is accurately matched with the passing parameters.
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_info @lastname varchar (40), @firstname varchar (20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastnameGO
AU_INFO stored procedures can be performed by:
EXECUTE au_info 'Dull', 'Ann' - OrEXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' - OrEXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' - OrEXEC au_info 'Dull' , 'Ann' - OREXEC AU_INFO @LastName = 'DULL', @firstname = 'Ann' - OREXEC AU_INFO @firstName = 'Ann', @lastname = 'DULL' If the process is the first statement in the batch Use:
AU_INFO 'DULL', 'Ann' - ORAU_INFO @LastName = 'DULL', @firstname = 'Ann' - ORAU_INFO @firstname = 'Ann', @lastname = 'DULL'
C. Using the stored procedure under the simple process with wildcard parameters, only the designated author (providing a name), a published book, and publishing agency. This stored procedure matches the passable parameters, and if the parameters are not provided, the preset default value is used.
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2 @lastname varchar (30) = 'D%', @firstname varchar (18) = '%' AS SELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_idWHERE au_fname LIKE @firstname And au_lname like @lastnamego
The AU_INFO2 stored procedure can be performed in a variety of combinations. Some combinations are listed below:
EXECUTE au_info2-- OrEXECUTE au_info2 'Wh%' - OrEXECUTE au_info2 @firstname = 'A%' - OrEXECUTE au_info2 '[CK] ars [OE] n' - OrEXECUTE au_info2 'Hunter', 'Sheryl' - OrEXECUTE au_info2 'H%', 's%'
D. Use the Output parameter OUTPUT parameter to allow external processes, batch or plurality of Transact-SQL statements to access a value set during process execution. The following example creates a stored procedure (Titles_Sum) and uses an optional input parameter and an output parameter. First, create a process:
USE pubsGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_sum' AND type = 'P') DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum @@ TITLE varchar (40) = '%', @@ SUM money OUTPUTASSELECT 'Title Name' = titleFROM Titles where title Like @@ Title Select @@ Sum = SUM (Price) from title @@ TitleGo
Next, the OUTPUT parameter is used to control the streaming language.
Note Output variables must be defined when creating a table and using this variable.
The parameter name and variable name do not necessarily match, but the data type and parameter location must match (unless @@ sum = variable form).
DECLARE @@ TOTALCOST moneyEXECUTE titles_sum 'The%', @@ TOTALCOST OUTPUTIF @@ TOTALCOST <200 BEGIN PRINT '' PRINT 'All of these titles can be purchased for less than $ 200.'ENDELSE SELECT' The total cost of these titles is $ ' RTRIM (Cast (@@ Totalcost As Varchar (20)))
The following is the result set:
Title name ------------------------------------------------ ------------------------ The Busy Executive's Database Guidethe Gourmet Microwavethe Psychology of Computer Cooking
(3 row (s) affected)
Warning, Null Value Eliminated from aggregate. All of these titles Can Be Purchased for Less Than $ 200.
E. Using the OUTPUT cursor parameter OUTPUT cursor parameters to transfer the local cursor of the stored procedure to batch, stored procedures, or triggers.
First, create the following procedure, declare and open a cursor on the Titles table:
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P') DROP PROCEDURE titles_cursorGOCREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUTASSET @titles_cursor = CURSORFORWARD_ONLY STATIC FORSELECT * FROM titlesOPEN @titles_cursorGO
Next, a batch of batch is performed, declare a local cursor variable, perform the above process to assign the cursor to the local variable, then extract from the cursor.
USE pubsGODECLARE @MyCursor CURSOREXEC titles_cursor @titles_cursor = @MyCursor OUTPUTWHILE (@@ FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursorENDCLOSE @MyCursorDEALLOCATE @MyCursorGO
F. Using the Recompile option If the parameters provided for the process are not typical parameters, and the new execution plan should not cache or store in memory, the with Recompile clause will be helpful.
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE titles_by_authorGOCREATE PROCEDURE titles_by_author @@ LNAME_PATTERN varchar (30) = '%' WITH RECOMPILEASSELECT RTRIM (au_fname) '' RTRIM (au_lname AS 'AUTHORS FULL Name', Title As TitleFrom Authors a inner Join Titleauthor Ta on A.au_id = ta.au_id inner join titles t on ta.title_id = t.title_idwhere au_lname like @@ lname_patterngo
G. The text of the stored procedure to the user using the Encryption option with Encryption clause. The following example creates an encryption process, using the sp_helptext system stored procedure to get information about the encryption process, then try to get information about the process directly from the SysComments table.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encrypt_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH ENCRYPTIONASSELECT * FROM authorsGO
EXEC SP_HELPTEXT Encrypt_this
The following is the result set:
The Object's Comments Have Been Encrypted.
Next, select the identification number and text of the encrypted storage process content. Select C.ID, C.Text from SysComments C Inner Join Sysobjects O on C.ID = O.Idwhere O.Name = 'Encrypt_This'
The following is the result set:
Note The output of the TEXT column is displayed in a single line. When executed, this information will appear in the same row with the ID column information.
ID TEXT ---------- -------------------------------------- ---------------------- 1413580074 ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????? e ?????????????????????????????????
(1 row (s) affected)
H. Creating a User-defined System Store The following example creates a process, displaying the table name All tables starting with EMP and its corresponding index. If you do not specify a parameter, the process will return all tables (and indexes) starting with SYS.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_showindexes' AND type = 'P') DROP PROCEDURE sp_showindexesGOUSE masterGOCREATE PROCEDURE sp_showindexes @@ TABLE varchar (30) = 'sys%' AS SELECT o.name AS TABLE_NAME, i.name AS INDEX_NAME, INDID AS INDEX_IDFROM SYSINDEXES IINER JOIN SYSOBJECTS O On O.ID = I.ID Where = @@ TableGo Use Pubsexec sp_showindexes 'EMP%' Go
The following is the result set:
Table_name index_name index_id ------------------------------------------------------------- -employee employee_ind 1emoyee PK_EMP_ID 2
(2 row (s) affected)
I. Using Delay Name Parked the following example display four processes and delay name parsing. Although referenced tables or columns are not present, each stored procedure can be created.
