How to get the list of columns in a database?

Using   ‘Syscolumns’ we can get the list of columns in a particular database.

EXAMPLE:

1.       It will return the number of rows with other details for ‘columns’ in a database.

SELECT * FROM Syscolumns

2.       It will return the number of rows with other details for a particular column data type in a database.

SELECT * FROM Syscolumns where xtype=’n’

Where n is xtype values.

Following are the possible values of xtype

Value	Object
127	Bigint
173	Binary
104	Bit
175	Char
61	Datetime
106	Decimal
62	Float
34	Image
56	Int
60	Money
239	Nchar
99	Ntext
108	Numeric
231	Nvarchar
58	Smalldatetime
52	Smallint
122	Smallmoney
98	Sql_variant
231	Sysname
35	Text
189	Timestamp
48	Tinyint
36	Uniqueidentifier
165	Varbinary
167	varchar

Suppose you want to know how many varchar datatype columns in your database

SELECT * FROM Syscolumns where xtype=167

It will return all the columns details whose datatype is ‘varchar’.

If you want the list of xtype,
Run following script



It will give you list of all xtype.SELECT xtype, name FROM systypes