Useful Stored procedures in SQL Server -- Final Part

6) SP_HELPCONSTRAINT: SP_HELPCONSTRAINTS [TABLE]
The sp_helpconstraint stored procedure is used to display information about the constraints created on the columns based on the input parameter. Constraint information like contains type, name, delete action, update action, and status of constraints. The below sample code displays the constraints information on Employee Table.
USE Pubs;
GO
EXEC sp_helpconstraint 'HR.Employees';
GO

7) SP_RENAME: SP_RENAME OLD_NAME, NEW_NAME, TYPE_OF_OBJECT
The sp_rename stored procedure is used to modify the names of database objects or user-defined data types. In SQL Server each object is associated with Object ID. When you provide a new name for an existing database object, other database objects using the renamed object continue to refer to the renamed object.
old_name: parameter refers to the existing object name.
new_name: parameter refers to the name you want to assign.
type_of_object: parameter refers to the type of database object whose name you want to modify.
Renaming a table
USE Pubs
GO
EXEC sp_rename 'HR.Employees', 'EMP';
GO

Renaming a column
USE Pubs
GO
EXEC sp_rename 'HR.Employees.EmployeeID', 'EMPID', 'COLUMN';
GO

8) SP_PASSWORD: SP_PASSWORD OLD_PASS,NEW_PASS [, LOGIN_ID]
Your password is shared and wants to change the password then you need to use sp_password stored procedure. The sp_password stored procedure is used to modify the password of a login ID. You need to pass the old password of a login ID as a parameter to create a new password. You need to specify the old password as NULL when you modify the password of the SA login ID. Microsoft is planning to remove this stored procedure usage in future versions and suggesting us to use “ALTER LOGIN” command.

9) SP_WHO
The sp_who stored procedure is used to display all database users who are currently logged onto SQL Server. The stored procedure also displays the processes currently running on SQL Server. Look at the below example to report all current users
USE master
GO
EXEC sp_who
GO

10) SP_PRIMARYKEY: SP_PRIMARYKEY TABLE,COLUMN1[, COLUMN2,....COLUMN(N)]
If you don’t know the syntax of primary key creation then this is the right procedure which you can use to create primary key. This stored procedure is used to apply the primary key constraint on a column or columns that is passed as a parameter. The column1, column2, and column(n) parameters contain the column names on which you want to create the primary key constraint. A primary key can contain more than one column.
Use Pubs
GO
SP_PRIMARYKEY 'HR.EMP','empid
GO

11) SP_RECOMPILE: SP_RECOMPILE
The sp_recompile stored procedure is used to recompile certain database objects. When you create a database object for the first time, the database object is compiled and a query plan is generated if all the dependencies are met. If you change the data in database objects, the stored procedures and triggers should be recompiled because the query plans generated for the database objects are no longer valid and new query plans should be generated. The below example causes stored procedures that are using EMP table to be recompiled the next time they are run.
USE Pubs;
GO
EXEC sp_recompile N'HR.EMP'
GO