SQL Server/T-SQL Tutorial/System Tables Views/syscolumns
Query syscolumns
<source lang="sql">
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></source>
Selecting the length of a column from syscolumns.
<source lang="sql">
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></source>
