SQL Server/T-SQL Tutorial/System Functions/sp help
Display table structure before and after altering command
<source lang="sql">
6> CREATE TABLE Employees 7> ( 8> EmployeeID int IDENTITY NOT NULL, 9> FirstName varchar(25) NOT NULL, 10> MiddleInitial char(1) NULL, 11> LastName varchar(25) NOT NULL, 12> Title varchar(25) NOT NULL, 13> SSN varchar(11) NOT NULL, 14> Salary money NOT NULL, 15> PriorSalary money NOT NULL, 16> LastRaise AS Salary - PriorSalary, 17> HireDate smalldatetime NOT NULL, 18> TerminationDate smalldatetime NULL, 19> ManagerEmpID int NOT NULL, 20> Department varchar(25) NOT NULL 21> ) 22> GO 1> 2> EXEC sp_help Employees 3> GO Name Owner
Type Created_datetime
-----------------------------------------------------------------------
------------------------------- -----------------------
Employees dbo
user table 2008-08-17 13:06:18.750
Column_name Type
Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNu
llInSource Collation
-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------
EmployeeID int
no 4 10 0 no (n/a) (n/a)
NULL
FirstName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
MiddleInitial char
no 1 yes no yes
SQL_Latin1_General_CP1_CI_AS
LastName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Title varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
SSN varchar
no 11 no no no
SQL_Latin1_General_CP1_CI_AS
Salary money
no 8 19 4 no (n/a) (n/a)
NULL
PriorSalary money
no 8 19 4 no (n/a) (n/a)
NULL
LastRaise money
yes 8 19 4 yes (n/a) (n/a)
NULL
HireDate smalldatetime
no 4 no (n/a) (n/a)
NULL
TerminationDate smalldatetime
no 4 yes (n/a) (n/a)
NULL
ManagerEmpID int
no 4 10 0 no (n/a) (n/a)
NULL
Department varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment
Not For Replication
---------------------------------------- ------------------------------
-------------------
EmployeeID 1
1 0
RowGuidCol
No rowguidcol column defined. Data_located_on_filegroup
PRIMARY The object "Employees" does not have any indexes, or you do not have permissions. No constraints are defined on object "Employees", or you do not have permissions. No foreign keys reference table "Employees", or you do not have permissions on referencing tables. Table is referenced by views
1> ALTER TABLE Employees 2> ADD CONSTRAINT PK_EmployeeID 3> PRIMARY KEY (EmployeeID) 4> GO 1> 2> EXEC sp_help Employees 3> GO Name Owner
Type Created_datetime
-----------------------------------------------------------------------
------------------------------- -----------------------
Employees dbo
user table 2008-08-17 13:06:18.750
Column_name Type
Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNu
llInSource Collation
-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------
EmployeeID int
no 4 10 0 no (n/a) (n/a)
NULL
FirstName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
MiddleInitial char
no 1 yes no yes
SQL_Latin1_General_CP1_CI_AS
LastName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Title varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
SSN varchar
no 11 no no no
SQL_Latin1_General_CP1_CI_AS
Salary money
no 8 19 4 no (n/a) (n/a)
NULL
PriorSalary money
no 8 19 4 no (n/a) (n/a)
NULL
LastRaise money
yes 8 19 4 yes (n/a) (n/a)
NULL
HireDate smalldatetime
no 4 no (n/a) (n/a)
NULL
TerminationDate smalldatetime
no 4 yes (n/a) (n/a)
NULL
ManagerEmpID int
no 4 10 0 no (n/a) (n/a)
NULL
Department varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment
Not For Replication
---------------------------------------- ------------------------------
-------------------
EmployeeID 1
1 0
RowGuidCol
No rowguidcol column defined. Data_located_on_filegroup
PRIMARY index_name index_description
index_keys
-----------------------------------------------------------------------
------------------------------------------------------------
PK_EmployeeID clustered, unique, primary key located on PRIMARY
EmployeeID
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
PRIMARY KEY (clustered) PK_EmployeeID
(n/a) (n/a) (n/a) (n/a) EmployeeID
No foreign keys reference table "Employees", or you do not have permissions on referencing tables.
Table is referenced by views
1> 2> drop table Employees; 3> GO 1></source>
EXEC sp_help on a table
<source lang="sql">
4> CREATE TABLE Customers 5> ( 6> CustomerNo int IDENTITY NOT NULL, 7> CustomerName varchar(30) NOT NULL, 8> Address1 varchar(30) NOT NULL, 9> Address2 varchar(30) NOT NULL, 10> City varchar(20) NOT NULL, 11> State char(2) NOT NULL, 12> Zip varchar(10) NOT NULL, 13> Contact varchar(25) NOT NULL, 14> Phone char(15) NOT NULL, 15> FedIDNo varchar(9) NOT NULL, 16> DateInSystem smalldatetime NOT NULL 17> ) 18> GO 1> EXEC sp_help Customers 2> GO Name Owner
Type Created_datetime
-----------------------------------------------------------------------
------------------------------- -----------------------
Customers dbo
user table 2008-08-18 20:36:02.103
Column_name Type
Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNu
llInSource Collation
-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------
CustomerNo int
no 4 10 0 no (n/a) (n/a)
NULL
CustomerName varchar
no 30 no no no
SQL_Latin1_General_CP1_CI_AS
Address1 varchar
no 30 no no no
SQL_Latin1_General_CP1_CI_AS
Address2 varchar
no 30 no no no
SQL_Latin1_General_CP1_CI_AS
City varchar
no 20 no no no
SQL_Latin1_General_CP1_CI_AS
State char
no 2 no no no
SQL_Latin1_General_CP1_CI_AS
Zip varchar
no 10 no no no
SQL_Latin1_General_CP1_CI_AS
Contact varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Phone char
no 15 no no no
SQL_Latin1_General_CP1_CI_AS
FedIDNo varchar
no 9 no no no
SQL_Latin1_General_CP1_CI_AS
DateInSystem smalldatetime
no 4 no (n/a) (n/a)
NULL
Identity Seed Increment
Not For Replication
---------------------------------------- ------------------------------
-------------------
CustomerNo 1
1 0
RowGuidCol
No rowguidcol column defined. Data_located_on_filegroup
PRIMARY The object "Customers" does not have any indexes, or you do not have permissions. No constraints are defined on object "Customers", or you do not have permissions. No foreign keys reference table "Customers", or you do not have permissions on referencing tables. No views with schema binding reference table "Customers". 1> 2> drop table Customers; 3> GO 1></source>
The system procedure sp_help displays information about one or more database objects.
<source lang="sql">
7> CREATE TABLE employee( 8> id INTEGER NOT NULL PRIMARY KEY, 9> first_name VARCHAR(10), 10> last_name VARCHAR(10), 11> salary DECIMAL(10,2), 12> start_Date DATETIME, 13> region VARCHAR(10), 14> city VARCHAR(20), 15> managerid INTEGER 16> ); 17> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> exec sp_help employee 3> GO Name Owner
Type Created_datetime
-----------------------------------------------------------------------
------------------------------- -----------------------
employee dbo
user table 2008-08-07 21:23:08.280
Column_name Type
Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNu
llInSource Collation
-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------
id int
no 4 10 0 no (n/a) (n/a)
NULL
first_name varchar
no 10 yes no yes
SQL_Latin1_General_CP1_CI_AS
last_name varchar
no 10 yes no yes
SQL_Latin1_General_CP1_CI_AS
salary decimal
no 9 10 2 yes (n/a) (n/a)
NULL
start_Date datetime
no 8 yes (n/a) (n/a)
NULL
region varchar
no 10 yes no yes
SQL_Latin1_General_CP1_CI_AS
city varchar
no 20 yes no yes
SQL_Latin1_General_CP1_CI_AS
managerid int
no 4 10 0 yes (n/a) (n/a)
NULL
Identity Seed Increment
Not For Replication
---------------------------------------- ------------------------------
-------------------
No identity column defined. NULL
NULL NULL
RowGuidCol
No rowguidcol column defined. Data_located_on_filegroup
PRIMARY index_name index_description
index_keys
-----------------------------------------------------------------------
------------------------------------------------------------
PK__employee__5575A085 clustered, unique, primary key located on PRIMARY
id
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
PRIMARY KEY (clustered) PK__employee__5575A085
(n/a) (n/a) (n/a) (n/a) id
No foreign keys reference table "employee", or you do not have permissions on referencing tables.
No views with schema binding reference table "employee".
1>
2> drop table employee;
3> GO
1>
2></source>
