Home » Tip Bank » Security » SQL Server

Language: SQL
Expertise: Advanced

Oct 18, 2005

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.ID
WHERE  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.