Overview:
I would like to give you an overview on various system defined stored procedures that are available in SQL Server.
Introduction:
There 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:
1) SP_RENAMEDB: SP_RENAMEDB OLD_NAME, NEW_NAME
This stored procedure is to rename the database from old names to new name. Microsoft is planning to remove this from future releases and also suggesting to use ALTER DATABASE command to implement the similar functionality.
Let’s look at the sample example
USE MASTER;
GO
CREATE DATABASE BANK_DB;
GO
EXEC SP_RENAMEDB N'BANK_DB', N'INVESTMENT_BANK_DB';
GO
SELECT NAME, DATABASE_ID, MODIFIED_DATE
FROM SYS.DATABASES
WHERE NAME = N'INVESTMENT_BANK_DB';
GO
We are creating a Database name called BANK_DB and then later we are modifying/renaming it to INVESTMENT_BANK_DB.
Query the SYS.DATABASES table to see whether it’s modified or not.
2) SP_HELPUSER: SP_HELPUSER [NAME_OF_USER]
The sp_helpuser stored procedure is used to display the user(s) and role(s) present in a database based on the input. Name of the user is optional filed; if you pass a specific user then it displays information, such as user name, login name, group name, default database, user ID, and sid of the user. The procedure displays information about the current database user if no parameter is passed.
EXEC sp_helpuser
The above stored procedure is used to display all the users in the current database.
3) SP_HELP: SP_HELP [OBJECT | USER_DEFINED_DATATYPE]
The sp_help stored procedure is used to display information about a database object that is passed as a parameter.
The parameter is optional. If you want to display all the objects with in the current database then don’t pass any parameter. If you are looking for specific objects information then pass the object name.
USE master
EXEC sp_help
The above code is used to display information about each object in Sysobjects.
4) SP_COLUMNS: SP_COLUMNS OBJECT, [OWNER_NAME], [TABLE_QUALIFIER], [COLUMN_NAME], [ODBCVER]
The sp_columns stored procedure is used to display information about table columns or view columns. The procedure accepts the table or view name as a parameter.
OBJECT: This is nothing but table or view.
OWNER_NAME: Is nothing but owner of object. This is optional Parameter.
TABLE_QUALIFIER: parameter refers to the database name in which the table or view exists. This is optional Parameter.
COLUMN_NAME: parameter specifies the name of a column that you pass as a parameter whose information is to be displayed.
ODBCVER: Parameter specifies the ODBC version used.
USE Pubs
GO
EXEC sp_columns @table_name = N'Employee',
@table_owner = N'HR';
5) SP_HELPINDEX: SP_HELPINDEX (TABLE)
The sp_helpindex stored procedure is used to display the indexes that are created on a table based on the input parameter. In addition to the indexes it will also display the columns which are part of the indexes.
USE Pubs;
GO
EXEC sp_helpindex N'HR.Employee';
GO
Keep watching for more information
|
| Author: Fazal Vahora 20 Aug 2008 | Member Level: Bronze Points : 0 |
hi,vijaya
Article is very good.
|