SQL Server/T-SQL Tutorial/System Tables Views/syscolumns

Query syscolumns

3> CREATE TABLE customer
4> (
5> cust_id       smallint       NOT NULL,
6> cust_name     varchar(50)    NOT NULL,
7> cust_addr1    varchar(50)    NOT NULL,
8> cust_addr2    varchar(50)    NOT NULL,
9> cust_city     varchar(50)    NOT NULL,
10> cust_state    char(2)        NOT NULL,
11> cust_zip      varchar(10)    NOT NULL,
12> cust_phone    varchar(10)    NOT NULL,
13> cust_fax      varchar(20)    NOT NULL,
14> cust_email    varchar(30)    NOT NULL,
15> cust_web_url  varchar(20)    NOT NULL
16> )
17> GO
1>
2> SELECT colid, name, xtype, length, xusertype, offset
3> FROM syscolumns WHERE id=object_id("customer")
4> GO
colid  name                                                                                                                             xtype length xusertype offset
------ -------------------------------------------------------------------------------------------------------------------------------- ----- ------ --------- ------
     1 cust_id                                                                                                                             52      2        52      2
     2 cust_name                                                                                                                          167     50       167     -1
     3 cust_addr1                                                                                                                         167     50       167     -2
     4 cust_addr2                                                                                                                         167     50       167     -3
     5 cust_city                                                                                                                          167     50       167     -4
     6 cust_state                                                                                                                         175      2       175      4
     7 cust_zip                                                                                                                           167     10       167     -5
     8 cust_phone                                                                                                                         167     10       167     -6
     9 cust_fax                                                                                                                           167     20       167     -7
    10 cust_email                                                                                                                         167     30       167     -8
    11 cust_web_url                                                                                                                       167     20       167     -9
(11 rows affected)
1>
2> drop table customer;
3> GO
1>

Selecting the length of a column from syscolumns.

4>
5>
6> CREATE TABLE publishers(
7>    pub_id         char(4)           NOT NULL,
8>    pub_name       varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    country        varchar(30)           NULL DEFAULT("USA")
12> )
13> GO
1>
2>
3> insert publishers values("1", "Publisher A", "Vancouver",  "MA", "USA")
4> insert publishers values("2", "Publisher B", "Washington", "DC", "USA")
5> insert publishers values("3", "Publisher C", "Berkeley",   "CA", "USA")
6> insert publishers values("4", "Publisher D", "New York",   "NY", "USA")
7> insert publishers values("5", "Publisher E", "Chicago",    "IL", "USA")
8> insert publishers values("6", "Publisher F", "Dallas",     "TX", "USA")
9> insert publishers values("7", "Publisher G", "Vancouver",  "BC", "Canada")
10> insert publishers values("8", "Publisher H", "Paris",      NULL, "France")
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>      SELECT    length
3>      FROM      syscolumns
4>      WHERE     id = OBJECT_ID("publishers")
5>      AND       name = "pub_id"
6>
7>
8> drop table publishers;
9> GO
length
------
     4
(1 rows affected)
1>
2>