Built in Stored Procedures in SQL Server

Discussion in 'SQL Server' started by MinalS, Feb 5, 2015.

  1. 1. Information about user



    There are requirements when user wants to know that who is connected to the SQL Server. The T-SQL provides the stored procedure sp_who. It helps know who all are connected to the to the computer.

    The syntax for the stored procedure is as mentioned below:

    Code:
    sp_who [ [ @login_name = ] 'login'  | session ID | 'ACTIVE' ]
    
    User can execute the procedure without arguments.

    Execute the stored procedure in the SQL Server application.
    Code:
    sp_who
    
    The following output is generated when the stored procedure is executed.

    [​IMG]

    The following columns are generated when the stored procedure is executed.
    1. spid: Every row in the column contains a unique value. It is useful for identifying the process. It is also known as session ID.
    2. ecid: It is known as execution context ID. It is useful for allocating threads to applications on the computer.
    3. status: It is useful to specify the action associated with the spid column. There are several values for the status parameter. They are as listed below:
      1. running: It states that the process is executing in the system.
      2. dormant: It states that the process is associated with the corresponding spid and not performing action.
      3. background: It states that the process is working behind the screen.
      4. rollback: It states that the process should restart the task.
      5. pending: The process has to wait for other task.
      6. runnable: The process is ready to perform action.
      7. suspended: The process must wait for the intermediate action to complete.
    4. loginame: It is the name of the user who owns the spid.
    5. hostname: It is the name of the computer with the login name user is using.
    6. blk: If the spid is blocked, the blk column shows a value. Else it displays 0.
    7. dbname: It is the database name user is currently using
    8. cmd: It is the last SQL command user has issued.
    9. request_id: It is the column stating an identifier which is an integer.
    User can enter the session ID as a parameter to the stored procedure. The session ID is a small integer.
    Code:
    sp_who 10;
    
    The result is the status of the process with the associated session ID.

    [​IMG]

    User can pass the ACTIVE argument to the stored procedure. It will show all the active sessionID in the procedure.
    Code:
    sp_who ACTIVE;
    
    [​IMG]

    2. Information about Database



    User can access the information about the database. The sp_help stored procedure provides user with the necessary information. The syntax for the procedure is as shown below:
    Code:
    sp_helpdb [  [ @dbname = ] 'name' ]
    
    The argument in the stored procedure is optional. It provides the information about the database, names, sizes, dates of creation.

    Execute the following stored procedure to execute the command.
    Code:
    sp_helpdb master;
    
    The result generated by the execution of the stored procedure is as shown below:

    [​IMG]

    3. Information about file group



    The T-SQL provides the sp_helpfilegroup stored procedure to get the information about the file groups in the database.

    The syntax for the stored procedure is as shown below:
    Code:
    sp_helpfilegroup [ [ @filegroupname = ] 'name' ] 
    
    The argument is optional parameter.

    Execute the stored procedure and the following output is generated.
    Code:
    sp_helpfilegroup;
    
    [​IMG]

    4. Information about an Object



    The sp_help stored procedure is used to get the information about an object. The syntax for the stored procedure is as shown below:
    Code:
    sp_help [ [ @objname = ] 'name' ]
    
    The stored procedure contains an optional parameter. Without argument, it generates an information about the database currently used by the system.

    The following code snippet demonstrates the stored procedure.
    Code:
    sp_help;
    GO
    
    The result contains the information about the database used in the system.

    [​IMG]

    5. Size of Object



    User can check the memory of the database that is used by the objects. The sp_spaceused stored procedure is executed.

    The syntax for the procedure is as shown below:

    Code:
    sp_spaceused [ [ @objname = ] 'objname' ]
    [ , [ @updateusage= ] 'updateusage' ] 
    
    If the user does not specify any arguments, user must select the database.

    Execute the following code to demonstrate the stored procedure without arguments.

    Code:
    sp_spaceused;
    GO
    
    The following output is generated after execution.

    [​IMG]

    6. Information about the columns in a table



    User can get the information about the table columns by executing the stored procedure as sp_columns. The syntax for the stored procedure is as shown below:

    Code:
    sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ] 
    [ , [ @table_qualifier= ] qualifier ]
    [ , [ @column_name = ] column ]
    [ , [ @ODBCVer = ] ODBCVer ]
    
    It can have many arguments in the declaration. User must use one as the table name for checking the column value.

    Execute the following code to demonstrate the use of the student table.
    Code:
    sp_columns student
    GO
    
    The output generated after executing the stored procedure is as shown below:

    [​IMG]

    7. Server Information



    When user needs the information about the server, the sp_monitor stored procedure is used. There are several value generated which helps user to analyze the CPU functionality.

    The following code is executed to view the server information.
    Code:
    sp_monitor;
    GO
    
    The output generated after executing the procedure is as shown below:

    [​IMG]

    In the above result, the values as the CPU status, packets information, connections related to the specific server are displayed.

    8. Updating View



    User can refresh the view using the sp_refreshview stored procedure. The syntax for the stored procedure is as shown below:
    Code:
    sp_refreshview [ @viewname = ] 'viewname' 
    
    viewname is the name of the view.

    The following code is used to refresh the view.
    Code:
    sp_refreshview view1;
    GO
    

    9. Deleting user defined data type



    Sometimes user creates a custom data type for the database. When user does not need the data type, it can be removed as sp_droptype stored procedure. The syntax for the stored procedure is as shown below:
    Code:
    sp_droptype [ @typename= ] 'type'
    
    [ @typename= ] 'type' is the name of the user defined data type. It returns the value as 0 or 1.

    The example to demonstrate the use of stored procedure is as shown below:
    Code:
    sp_droptype OddNumber;
    GO
    

    10. Automated stored procedure option



    The stored procedures execute and the results are generated later. There are requirements when user wants to execute the procedure automatically. The sp_procoption stored procedure is used.

    The syntax for the procedure is as shown below:
    Code:
    sp_procoption [ @ProcName = ] 'procedure' 
    , [@OptionName = ] 'option'
    , [@OptionValue = ] 'value'
    
    Where,

    [@ProcName = ] 'procedure' is the name of the procedure.
    [ @OptionName= ] 'option' is the name of the option set.
    [ @OptionValue= ] 'value' is to set the option.

    11. Setting the trigger order



    User can specify AFTER trigger associated with the table to be fired in which position. The AFTER triggers will be executed in an unexpected order.

    The syntax of the stored procedure is as mentioned below:

    Code:
    sp_settriggerorder [ @triggername= ] ' [ triggerschema. ] triggername' 
    , [ @order= ] 'value'
    , [ @stmttype= ] 'statement_type'
    [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]
    
    The following code shows the use of the stored procedure.
    Code:
    sp_settriggerorder @triggername = 'Trigger1' , @order = 'first' , @stmttype = 'UPDATE' 
    

    12. Catalogs in the linked server



    It returns the list of catalogs in the linked server specified by the user.
    The syntax for the procedure for viewing the list is as shown below:
    Code:
    sp_catalogs [ @server_name = ] 'linked_svr'
    
    [@server_name] is the name of the server
    linked_svr is the sysname.

    The following code snippet demonstrates the use of the procedure.
    Code:
     EXEC sp_catalogs 'OLE DB ODBC Linked Server #2'
    

    13. Adding an alias



    When the user and login needs to be mapped, the sp_addalias procedure is used. It provides user with the ability to provide permissions for roles.

    The syntax for the procedure is as shown below:
    Code:
    sp_addalias [ @loginame = ] 'login'
    , [ @name_in_db = ] 'alias_user'
    
    where,

    [ @loginame = ] 'login' is the name of the login to be added as a alias.
    [ @name_in_db ] 'alias_user' is the name of the user who login is mapped.

    The following code snippet demonstrates the stored procedure.
    Code:
    EXEC sp_addalias 'Manthan' 'Nitin'
    
     
    Last edited by a moderator: Jan 21, 2017
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice