System Procedures and their usage

1. SP_REFRESHVIEW

Refreshes the metadata for the specified view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Syntax
sp_refreshview [ @viewname = ] 'viewname'

Arguments

[@viewname =] 'viewname' Is the name of the view. viewname, which can be a multipart identifier, is nvarchar, with no default.

Return Code Values

0 (success) or a nonzero number (failure)

Permissions
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the view owner can execute sp_refreshview on a view.

Examples

This example refreshes the metadata for the view titleview.

exec sp_refreshview titleview

2. SP_RENAME

Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.

Syntax
sp_rename [ @objname = ] 'object_name' ,
    [ @newname = ] 'new_name'
    [ , [ @objtype = ] 'object_type' ]

Arguments

[@objname =] 'object_name'

Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.

[@newname =] 'new_name'

Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.

[@objtype =] 'object_type'

Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.

Value	Description
COLUMN	A column to be renamed.
DATABASE	A user-defined database. This option is required when renaming a database.
INDEX	A user-defined index.
OBJECT	An item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.
USERDATATYPE	A user-defined data type added by executing sp_addtype.

Return Code Values
 0 (success) or a nonzero number (failure)

Remarks
You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.

When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.
 
Important  After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.
Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name.

Permissions
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename. Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with 'database' as the object_type.

Examples 

A. Rename a table

This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs' 

B. Rename a column

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

Happy Coding...

Keywords and their usage

1. COALESCE

Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments

expression  Is an expression of any type.

n  Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types

Returns the same value as expression.

Remarks
If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

Examples

In this example, the wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.

SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'wages')
   DROP TABLE wages
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52, 
   salary, 
   commission * num_sales) AS money) AS 'Total Salary' 
FROM wages
GO

Here is the result set:

Total Salary 
------------ 
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000

(12 row(s) affected)

2. NULLIF

 

Returns a null value if the two specified expressions are equivalent.

Syntax
NULLIF ( expression , expression )

Arguments

expression  Is a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

Return Types

Returns the same type as the first expression.

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.

Remarks
NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Examples

This example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that receive a budget as well as to include the budget value from the previous year (use the previous_year value, where the current_year is 0), combine the NULLIF and COALESCE functions.

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'budgets')
   DROP TABLE budgets
GO
SET NOCOUNT ON
CREATE TABLE budgets
(
   dept            tinyint   IDENTITY,
   current_year      decimal   NULL,
   previous_year   decimal   NULL
)
INSERT budgets VALUES(100000, 150000)
INSERT budgets VALUES(NULL, 300000)
INSERT budgets VALUES(0, 100000)
INSERT budgets VALUES(NULL, 150000)
INSERT budgets VALUES(300000, 250000)
GO  
SET NOCOUNT OFF
SELECT AVG(NULLIF(COALESCE(current_year,
   previous_year), 0.00)) AS 'Average Budget'
FROM budgets
GO

Here is the result set:

Average Budget                           
---------------------------------------- 
212500.000000

(1 row(s) affected)