Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Oct 18, 2005

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

Daljinder Singh
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap