SQL Server/T-SQL/System/columns
table syscolumns appears in the master database and in every user-defined database.
<source lang="sql">
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)
</source>
Using system table: columns
<source lang="sql">
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
</source>