SQL Server/T-SQL/System/columns
table syscolumns appears in the master database and in every user-defined database.
It contains a row for every column of a base table or a view and a row for each parameter in a stored procedure.
7>
8> select top 10 * from syscolumns;
9> GO
name id xtype typestat xusertype length xprec xscale colid xoffset
bitpos reserved colstat cdefault domain number colorder autoval
offset collationid l
anguage status type usertype printfmt
prec scale iscomputed isoutparam isnullable collation
tdscollation
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- -------- --------- ------ ----- ------ ------ -------
------ -------- ------- ----------- ----------- ------ -------- ---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ----------- -
---------- ------ ---- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------- ------ ----------- ----------- ----------- ----------- ---------------------------------------------------------
----------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rowsetid 4 127 1 127 8 19 0 1 0
0 0 0 0 0 0 1 NULL
0 0
0 0 63 0 NULL
19 0 0 0 0 NULL
0x0000000000
rowsetcolid 4 56 1 56 4 10 0 2 0
0 0 0 0 0 0 2 NULL
0 0
0 0 56 7 NULL
10 0 0 0 0 NULL
0x0000000000
hobtcolid 4 56 1 56 4 10 0 3 0
0 0 0 0 0 0 3 NULL
0 0
0 0 56 7 NULL
10 0 0 0 0 NULL
0x0000000000
status 4 56 1 56 4 10 0 4 0
0 0 0 0 0 0 4 NULL
0 0
0 0 56 7 NULL
10 0 0 0 0 NULL
0x0000000000
rcmodified 4 127 1 127 8 19 0 5 0
0 0 0 0 0 0 5 NULL
0 0
0 0 63 0 NULL
19 0 0 0 0 NULL
0x0000000000
maxinrowlen 4 52 1 52 2 5 0 6 0
0 0 0 0 0 0 6 NULL
0 0
0 0 52 6 NULL
5 0 0 0 0 NULL
0x0000000000
rowsetid 5 127 1 127 8 19 0 1 0
0 0 0 0 0 0 1 NULL
0 0
0 0 63 0 NULL
19 0 0 0 0 NULL
0x0000000000
ownertype 5 48 1 48 1 3 0 2 0
0 0 0 0 0 0 2 NULL
0 0
0 0 48 5 NULL
3 0 0 0 0 NULL
0x0000000000
idmajor 5 56 1 56 4 10 0 3 0
0 0 0 0 0 0 3 NULL
0 0
0 0 56 7 NULL
10 0 0 0 0 NULL
0x0000000000
idminor 5 56 1 56 4 10 0 4 0
0 0 0 0 0 0 4 NULL
0 0
0 0 56 7 NULL
10 0 0 0 0 NULL
0x0000000000
(10 rows affected)
Using system table: columns
USE Northwind
GO
1>
2> DECLARE @TBLName nvarchar(128)
3> SET @TBLName = N"Shippers"
4>
5> SELECT name, object_id FROM sys.tables WHERE name = @TBLName
6>
7> SELECT name, column_id, object_id
8> FROM sys.columns
9> WHERE object_id IN
10> (SELECT object_id
11> FROM sys.tables
12> WHERE name = @TBLName)
13>
14> GO