devxlogo

Find All User Tables, Columns, DataTypes, and Length

Find All User Tables, Columns, DataTypes, and Length

To determine all your user tables, plus their columns, datatypes, and length, type:

SELECT DISTINCT SO.Name AS "TableName",		 SC.ColID,		 SC.Name AS "ColumnName", 		 ST.Name AS "DataType", 		 SC.Length AS "Length",		 SC.Status AS "Status" FROM   SysObjects SO JOIN		(sysColumns SC JOIN sysTypes ST 		 ON SC.UserType = ST.UserType)		 ON SO.ID = SC.IDWHERE  SO.Xtype = 'U'ORDER BY SO.Name,SC.ColID

Alternatively, you could write a small application to split this query into two parts. The first part would get all the table names and IDs using sysobjects (to provide a heading if you’re saving this into, say, MS Word). The second part would retrieve all the column names and datatypes using those IDs.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist