Overview:
I would like to give you an overview on various system defined stored procedures that are available in SQL Server.
Introduction:
These are several predefined stored procedures are available in SQL Server which we are using in our day-to-day life to resolve some of the issues like below.
1) I want to see the all columns of the tables.
2) I want to see who are all logged into Database.
3) I want to see the Constraint information.
4) I want to see all the indexes which are created on a table.
5) I want to rename the objects etc.
Let’s get into the details:
If you haven't got a chance to refer my previous article on this, then check out the below link
http://www.dotnetspider.com/resources/19907-Useful-Stored-procedures-SQL-Server-Part.aspx
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 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
|
No responses found. Be the first to respond and make money from revenue sharing program.
|