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';
