SQL SERVER

*SysObjects

SQL Server sysobjects Table contains one row for each object created within a database. In
other words, it has a row for every constraint, default, log, rule, stored procedure, and so on
in the database.

Therefore, this table can be used to retrieve information about the database. We can use
xtype column in sysobjects table to get useful database information. This column specifies the
type for the row entry in sysobjects.

For example, you can find all the user tables in a database by using this query:

select * from sysobjects where xtype='U'

Similarly, you can find all the stored procedures in a database by using this query:

select * from sysobjects where xtype='P'

This is the list of all possible values for this column (xtype):

* C = CHECK constraint
* D = Default or DEFAULT constraint
* F = FOREIGN KEY constraint
* L = Log
* P = Stored procedure
* PK = PRIMARY KEY constraint (type is K)
* RF = Replication filter stored procedure
* S = System table
* TR = Trigger
* U = User table
* UQ = UNIQUE constraint (type is K)
* V = View
* X = Extended stored procedure

*object ID

This example returns the object ID for the authors table in the pubs database.

USE master
SELECT OBJECT_ID('pubs..authors')
--RESULT--
1977058079

(1 row(s) affected)

*OBJECT_NAME

This example returns the OBJECT_NAME for the authors table in the pubs database.

USE pubs
SELECT TABLE_CATALOG, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = OBJECT_NAME(1977058079)
Here is the result set:

TABLE_CATALOG TABLE_NAME
------------------------------ --------------
pubs authors

(1 row(s) affected)

*REPLACE

SELECT REPLACE('abcdefghicde','cde','xxx')

RESULt--abxxxfghixxx

*REPLICATE

Repeats a character expression for a specified number of times.

SELECT REPLICATE(au_fname, 2)
FROM authors
ORDER BY au_fname

Here is the result set:

----------------------
AbrahamAbraham
AkikoAkiko
AlbertAlbert
AnnAnn

*SUBSTRING

Returns part of a character, binary, text, or image expression.

SUBSTRING ( expression , start , length )

USE pubs
SELECT au_lname, SUBSTRING(au_fname, 1, 1)
FROM authors
ORDER BY au_lname

Here is the result set:

au_lname
---------------------------------------- -
Bennet A

*REVERSE

Returns the reverse of a character expression.

SELECT REVERSE(au_fname)
FROM authors
ORDER BY au_fname
GO

Here is the result set:

--------------------
maharbA
okikA
treblA

*RIGHT

Returns the right part of a character string with the specified number of characters.

ELECT RIGHT(au_fname, 5)
FROM authors
ORDER BY au_fname
GO

Here is the result set:

------------------
raham
Akiko
lbert

*CONTAINS

A. Use CONTAINS with <simple_term>

This example finds all products with a price of $15.00 that contain the word "bottles".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO

B. Use CONTAINS and phrase in <simple_term>

This example returns all products that contain either the phrase "sasquatch ale" or "steeleye
stout".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO

C. Use CONTAINS with <prefix_term>

This example returns all product names with at least one word starting with the prefix choc in
the ProductName column.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO
D. Use CONTAINS and OR with <prefix_term>

This example returns all category descriptions containing the strings "sea" or "bread".

USE Northwind
SELECT CategoryName
FROM Categories
WHERE CONTAINS(Description, '"sea*" OR "bread*"')
GO

E. Use CONTAINS with <proximity_term>

This example returns all product names that have the word "Boysenberry" near the word
"spread".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
GO

F. Use CONTAINS with <generation_term>

This example searches for all products with words of the form dry: dried, drying, and so on.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO

G. Use CONTAINS with <weighted_term>

This example searches for all product names containing the words spread, sauces, or relishes,
and different weightings are given to each word.

USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
GO

H. Use CONTAINS with variables

This example uses a variable instead of a specific search term.

USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
*CEILING

Returns the smallest integer greater than, or equal to, the given numeric expression.
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
GO

Here is the result set:

--------- --------- -------------------------
124.00 -123.00 0.00

(1 row(s) affected)

*CHARINDEX

Returns the starting position of the specified expression in a character string

USE pubs
GO
SELECT CHARINDEX('wonderful', notes, 5)//SELECT CHARINDEX('wonderful', notes) for zero
index
FROM titles
WHERE title_id = 'TC3218'

for wonderful starting with the fifth character in the notes

-- column.

*ROUND

Returns a numeric expression, rounded to the specified length or precision.

ROUND always returns a value. If length is negative and larger than the number of digits
before the

decimal point, ROUND returns 0.

Example Result
ROUND(748.58, -4) 0

ROUND returns a rounded numeric_expression, regardless of data type, when length is a

negative number.
Examples Result
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00

Examples
A. Use ROUND and estimates

This example shows two expressions illustrating that with the ROUND function the last digit is
always an

estimate.

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)

GO
Here is the result set:
----------- -----------
123.9990 124.0000

*DATALENGTH

Returns the number of bytes used to represent any expression.

ELECT length = DATALENGTH(pub_name), pub_name

FROM publishers
ORDER BY pub_name
GO

Here is the result set:

length pub_name
----------- ----------------------------------------
20 Algodata Infosystems
16 Binnet & Hardley
21 Five Lakes Publishing

*@@ROWCOUNT—will return no of records affected

*@@ERROR---Returns the error number for the last Transact-SQL statement executed.

*SET ROWCOUNT---
Causes Microsoft® SQL Server™ to stop processing the query after the specified
number of rows are returned.
SET ROWCOUNT 20
GO
delete from emp
GO
Set rowcount 20—will set no of effected rows to 20 .
Suppose emp contains 100 records then it will delete only 20 records
Select * from emp will also display only 20 records .
Insert query will insert only one record at time

*@@SPID--Returns the server process identifier (ID) of the current user process

*ABS--Returns the absolute, positive value of the given numeric expression.

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)

Here is the result set:----1.0 .0 1.0

*ISDATED

determines whether an input expression is a valid date.

This example checks the @datestring local variable for valid date data.

DECLARE @datestring varchar(8)

SET @datestring = '12/21/98'
SELECT ISDATE(@datestring)

Here is the result set:-- 1

*IS [NOT] NULL

Determines whether or not a given expression is NULL.

This example returns the title number and the advance amount for all books in which
either the advance amount is less than $5,000 or the advance is unknown (or NULL).
Note that the results shown are those returned after Example C has been executed.

USE pubs
SELECT title_id, advance
FROM titles
WHERE advance < $5000 OR advance IS NULL
ORDER BY title_id

*ISNUMERIC

Determines whether an expression is a valid numeric type.

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating
point number, money or decimal type; otherwise it returns 0. A return value of 1
guarantees that expression can be converted to one of these numeric types.

*LEFT

Returns the left part of a character string with the specified number of characters.

This example returns the five leftmost characters of each book title.

USE pubs
GO
SELECT LEFT(title, 5)
FROM titles
 ORDER BY title_id
GO

Here is the result set:--

The B
Cooki
You C
The G

SELECT LEFT('abcdefg',2)

Here is the result set:--ab

*LEN

Returns the number of characters, rather than the number of bytes, of the given string
expression, excluding trailing blanks

This example selects the number of characters and the data in CompanyName for
companies located in Finland.

USE Northwind
GO
SELECT LEN(CompanyName) AS 'Length', CompanyName
FROM Customers
WHERE Country = 'Finland'

Here is the result set:

Length CompanyName
----------- ------------------------------
14 Wartian Herkku
11 Wilman Kala

*LOWER

Returns a character expression after converting uppercase character data to lowercase.

This example uses the LOWER function, the UPPER function, and nests the UPPER
function inside the LOWER function in selecting book titles that have prices between
$11 and $20.

USE pubs
GO
SELECT LOWER(SUBSTRING(title, 1, 20)) AS Lower,
UPPER(SUBSTRING(title, 1, 20)) AS Upper,
LOWER(UPPER(SUBSTRING(title, 1, 20))) As LowerUpper
FROM titles
WHERE price between 11.00 and 20.00
GO

Here is the result set:

Lower Upper LowerUpper

-------------------- -------------------- --------------------
the busy executive's THE BUSY EXECUTIVE'S the busy executive's
cooking with compute COOKING WITH COMPUTE cooking with compute
straight talk about STRAIGHT TALK ABOUT straight talk about

*LTRIM

Returns a character expression after removing leading blanks.

This example uses LTRIM to remove leading spaces from a character variable.

DECLARE @string_to_trim varchar(60)

SET @string_to_trim = ' Five spaces are at the beginning of this
string.'
SELECT 'Here is the string without the leading spaces: ' +
LTRIM(@string_to_trim)
GO

Here is the result set:

------------------------------------------------------------------------
Here is the string without the leading spaces: Five spaces are at the beginning of
this string.

*POWER And WHILE

Returns the value of the given expression to the specified power.

 This example returns POWER results for 21 to 24.

DECLARE @value int, @counter int

SET @value = 2
SET @counter = 1

WHILE @counter < 5

BEGIN
SELECT POWER(@value, @counter)
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO

Result:-----------
2
(1 row(s) affected)
-----------4
(1 row(s) affected)
-----------
8
(1 row(s) affected)
-----------
16
(1 row(s) affected)

*DATEADD

Returns a new datetime value based on adding an interval to the specified date.
Syntax

DATEADD ( datepart , number, date )

Arguments

*Datepart

Is the parameter that specifies on which part of the date to return a new value. The table lists
the

dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

This example prints a listing of a time frame for titles in the pubs database. This time frame
represents

the existing publication date plus 21 days.

USE pubs
GO

SELECT pubdate AS timeframe FROM titles---------1991-06-12 00:00:00.000

SELECT DATEADD(day, 21, pubdate) AS timeframe-----1991-07-03 00:00:00.000
FROM titles

*DATEDIFF

Returns the number of date and time boundaries crossed between two specified dates.

This example determines the difference in days between the current date and the publication
date for

titles in the pubs database.

USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO

*SELECT DATENAME(month, getdate()) AS 'Month Name'---July

*DATEPART
Returns an integer representing the specified datepart of the specified date.

SELECT DATEPART(month, GETDATE())----7

*DAY
Returns an integer representing the day datepart of the specified date.

SELECT DAY('03/12/1998') AS 'Day Number'

GO

Here is the result set:

Day Number
------------
12

*SET DATEFORMAT

Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime
data.

SET DATEFORMAT mdy

GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
Result---1998-12-31 00:00:00.000

SET DATEFORMAT ydm

GO
DECLARE @datevar datetime
SET @datevar = '98/31/12'
SELECT @datevar
GO

SET DATEFORMAT ymd

GO
DECLARE @datevar datetime
SET @datevar = '98/12/31'
SELECT @datevar
GO

* STATS_DATE

Returns the date that the statistics for the specified index were last updated

This example returns the date of the last time that the statistics were updated for the specified
object.

USE master
GO
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'employee' AND o.id = i.id
GO

*DB_ID

Returns the database identification (ID) number.

USE master
SELECT name, DB_ID(name) AS DB_ID
FROM sysdatabases
ORDER BY dbid

Here is the result set:

name DB_ID
------------------------------ ------
master 1
tempdb 2
model 3
msdb 4

*DB_NAME

Returns the database name.

This example examines each database in sysdatabases using the database identification
number to determine

the database name.

USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
ORDER BY dbid
GO

Here is the result set:

dbid DB_NAME
------ ------------------------------
1 master
2 tempdb
3 model
4 msdb
5 pubs

* If Else

USE pubs
GO
DECLARE @msg varchar(255)
IF (SELECT COUNT(price)
FROM titles
WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 5

BEGIN

SET @msg = 'There are several books that are a good value between $10 and $20. These
books are: '
PRINT @msg
SELECT title
FROM titles
WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20
 END
ELSE
BEGIN

SET @msg = 'There are no books between $10 and $20. You might consider the following
books that are

under $10.'
PRINT @msg

END

* Compare queries using EXISTS and = ANY

This example shows two queries to find authors who live in the same city as a publisher. The
first query

uses = ANY and the second uses EXISTS. Note that both queries return the same information.

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
GO

-- Or, using = ANY

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)
GO

*Compare queries using EXISTS and IN

This example compares two queries that are semantically equivalent. The first query uses
EXISTS and the

second query uses IN. Note that both queries return the same information.

USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
 WHERE pub_id = publishers.pub_id
AND type = 'business')
GO

-- Or, using the IN clause:

USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
GO

* Use NOT EXISTS

NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no
rows are

returned by the subquery. This example finds the names of publishers who do not publish
business books.

USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
ORDER BY pub_name
GO

*CREATE RULE

Creates an object called a rule. When bound to a column or a user-defined data type, a rule
specifies the acceptable values that can be inserted into that column. Rules, a backward
compatibility feature, perform some of the same functions as check constraints. CHECK
constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred,
standard way to restrict the values in a column (multiple constraints can be defined on one or
multiple columns). A column or user-defined data type can have only one rule bound to it.
However, a column can have both a rule and one or more check constraints associated with it.
When this is true, all restrictions are evaluated.

A. Rule with a range

This example creates a rule that restricts the range of integers inserted into the column(s) to
which this rule is bound.

CREATE RULE range_rule

AS
@range >= $1000 AND @range < $20000
B. Rule with a list

This example creates a rule that restricts the actual values entered into the column or columns
(to which this rule is bound) to only those listed in the rule.

CREATE RULE list_rule

AS
@list IN ('1389', '0736', '0877')

C. Rule with a pattern

This example creates a rule to follow a pattern of any two characters followed by a hyphen,
any number of characters (or no characters), and ending with an integer from 0 through 9.

CREATE RULE pattern_rule

AS
@value LIKE '_ _-%[0-9]'

*CASE

USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

*Information Schema
The information schema views provided by SQL Server 2000 are used to retrieve some of the
metadata in this application. These views provide a wealth of metadata about SQL Server
databases. Just for fun, I tried using a few different views and found some really cool features.
The following SQL statement pulls a list of the tables and views in the database, sorted by
table name:
select * from INFORMATION_SCHEMA.Tables order by table_type, table_name

This SQL query pulls information about the stored procedures in the database, as shown in the
following:
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
And if that wasn't cool enough, check this out. The following code pulls the names of all the
stored procedures and the code to create them:
select routine_name, routine_definition
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'

In Sql Server, this kind of querying metadata of a database is possible through

Information_schema or the system tables.

Information_schema is an open standard formulated as part of Ansi Sql-92 standard and most
databases implement it to expose the metadata of a database. The system tables (sys.objects
etc) are Sql Server specific meta data storage tables.

Here are a few code samples that demonstrate the usefulness of information_schema.

To list all the tables in a database.

select * from information_schema.tables

List all the views in a database

select * from information_schema.tables where table_type=‘view’

List all the tables in ‘Northwind’ catalog excluding some of the system tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_CATALOG = ‘Northwind’
AND TABLE_TYPE = ‘BASE TABLE’
AND TABLE_NAME != ‘dtproperties’
AND TABLE_NAME != ’sysconstraints’
AND TABLE_NAME != ’syssegments’
AND TABLE_NAME != ’sysdiagrams’
ORDER BY TABLE_NAME ASC

List all the columns in the database that has the word ‘product’ in it.

select * from information_schema.columns where column_name=‘%product%’

Although information_schema.tables and information_schema.columns views offer most of the

information the views do not enumerate most the column level details. This is where the
COLUMNPROPERTY helps.

List all columns in the database that are identity fields.

SELECT INFORMATION_SCHEMA.COLUMNS.* from INFORMATION_SCHEMA.COLUMNS WHERE

(SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, ‘IsIdentity’)) = 1

List all columns in the database that are computed fields.

SELECT INFORMATION_SCHEMA.COLUMNS.* from information_schema.columns where

(SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, ‘IsComputed’))=1

There are two more important information_schema views that are useful in retrieving the table
constraints, keys and indexes. They are information_schema.table_constraints and
information_schema.key_column_usage

List all the primary key columns in the database.

SELECT K.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON K.CONSTRAINT_NAME =
C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = ‘PRIMARY KEY’

List all the foreign key constraints in the catalog.

SELECT K.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON K.CONSTRAINT_NAME =
C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = ‘FOREIGN KEY’

• EXEC sp_spaceused 'tablename'

• Result--

Name rows reserved data index_size unused

titles 18 40 KB 8 KB 32 KB 0 KB

*Listing triggers

SELECT name FROM sysobjects

WHERE type = 'TR'

List All tables in database

EXEC sp_tables
The returned results set contains a list of the tables (and views) in the current database.
The TABLE_NAME column gives the name of the table. The TABLE_TYPE column indicates if
the table is a TABLE, SYSTEM TABLE or a VIEW. The TABLE_OWNER column is also useful
as it shows the table's owner

*Find out which columns are in a database table

EXEC sp_columns 'sales'

The returned results set contains a list of the table's columns. There are quite a few columns
in the results set, so only the most useful will be described here:

• The COLUMN_NAME column gives the name of the column.

• The TYPE_NAME column gives the column's data type.
• The LENGTH column gives the column's data type length.
• The IS_NULLABLE column shows whether the column accepts null values.
 *Programmatically display a View's Transact-SQL

The system stored procedure sp_helptext will return a results set containing the lines of
Transact-SQL that comprise the View. For example, the following will return the text of the
stored procedure Order Details Extended's CREATE VIEW statement:

EXEC sp_helptext 'Order Details Extended'

*Find out which stored procedures are in a database

This is achieved by making use of the sp_stored_procedures system stored procedure,

EXEC sp_stored_procedures

The returned results set contains a list of the stored procedures in the database in which it is
executed. in the results set, the PROCEDURE_NAME column gives the name of the stored
procedure.

*Programmatically display a stored procedure's Transact-SQL

The system stored procedure sp_helptext will return a results set containing the lines of
Transact-SQL that comprise the stored procedure. For example, the following will return the
text of the stored procedure CustOrdersDetail's CREATE PROCEDURE statement:

EXEC sp_helptext 'CustOrdersDetail'

The text is contained within the Text column of the results set.

• SELECT @@SERVERNAME --returns current Server name

• @@ROWCOUNT--Returns the number of rows affected by the last statement.

Printing message on inserted and update on tables

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminderx' AND type = 'TR')
DROP TRIGGER reminderx
GO
CREATE TRIGGER reminderx
ON emp
FOR INSERT, UPDATE
AS print 'data inserted '
GO

select * from emp

insert into emp values('12','dev')

Result –
‘data inserted ‘
(1 row(s) affected)

*Roll back Transaction on deleting data

CREATE TRIGGER del ON [emp]

FOR DELETE
AS
IF (@@ROWCOUNT >1)
BEGIN
PRINT 'Can not remove emp'
PRINT 'Transaction has been canceled'
ROlLBACK
END

delete from emp

select * from emp

Result------
'Can not remove emp'
‘Transaction has been canceled’

*Begin –End

USE pubs
GO
CREATE TRIGGER deltitle
ON titles
FOR delete
AS
IF (SELECT COUNT(*) FROM deleted, sales
WHERE sales.title_id = deleted.title_id) > 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'You can't delete a title with sales.'
END

*Alter Trigger

USE pubs
GO
CREATE TRIGGER royalty_reminder
ON roysched
WITH ENCRYPTION
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO
-- Now, alter the trigger.
USE pubs
GO
ALTER TRIGGER royalty_reminder
ON roysched
FOR INSERT
AS RAISERROR (50009, 16, 10)
 *sp_help

Reports information about a database object (any object listed in the sysobjects
table), a user-defined data type, or a data type supplied by Microsoft® SQL Server

The sp_help procedure looks for an object in the current database only.

When name is not specified, sp_help lists object names, owners, and object types for
all objects in the current database. sp_helptrigger provides information about
triggers.

Permissions

Execute permissions default to the public role.

Examples

A. Return information about all objects

This example lists information about each object in sysobjects.

USE master
EXEC sp_help

B. Return information about a single object

This example displays information about the publishers table.

USE pubs
EXEC sp_help publishers

BACK UP DATABASE

backup database test to disk = 'e:\AdventureWorks_2008_Feb.bak'

with description = 'Full database backup to AdventureWorks',

name = 'Backup for Febrary, 2008, Week 2';