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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: