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>