Friday, September 4, 2009

retrieve table name type field name field type

The following sql command gets you the Table Names, and table names’ field names, field names’ datatype and datatype’s length from a DATABASE


SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid

No comments: