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