SQL Server/T-SQL/Table/Table Properties

To view the properties of a table

1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2>
3> --To view the properties of a table
4>
5> EXEC sp_help Employee
6> GO
Name                                                                                                                             Owner
                                                                              Type                            Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------
----------------------------------------------------------------------------- ------------------------------- -----------------------
employee                                                                                                                         dbo
                                                                              user table                      2006-10-09 15:11:03.670

Column_name                                                                                                                      Type
                                                                              Computed                            Length      Prec  Scale Nullable
ailingBlanks                  FixedLenNullInSource                Collation
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------
----------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- -------------------
----------------------------- ----------------------------------- -------------------------------------------------------------------------------------------
--------------
ID                                                                                                                               int
                                                                              no                                            4 10    0     yes
                              (n/a)                               NULL
name                                                                                                                             nvarchar
                                                                              no                                           20             yes
                              (n/a)                               SQL_Latin1_General_CP1_CI_AS
salary                                                                                                                           int
                                                                              no                                            4 10    0     yes
                              (n/a)                               NULL
start_date                                                                                                                       datetime
                                                                              no                                            8             yes
                              (n/a)                               NULL
city                                                                                                                             nvarchar
                                                                              no                                           20             yes
                              (n/a)                               SQL_Latin1_General_CP1_CI_AS
region                                                                                                                           char
                                                                              no                                            1             yes
                              yes                                 SQL_Latin1_General_CP1_CI_AS

Identity                                                                                                                         Seed
                               Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------
------------------------------ -------------------
No identity column defined.
                          NULL                NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object "Employee" does not have any indexes, or you do not have permissions.
No constraints are defined on object "Employee", or you do not have permissions.
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>
3>
4>
5> drop table employee
6> GO
1>