Recently while working with a client when they asked, "How does one list all the tables and views, with their fields and descriptions of a SQL database?"
To list all the tables and views fields and their corresponding descriptions, within an SQL database:
SELECT sysobjects.name as [Table Name], syscolumns.name as [Column Name],
tableprop.value as [Table Description], colprop.value as [Col Description]
FROM
sysobjects INNER JOIN syscolumns ON syscolumns.id = sysobjects.id
left outer JOIN sysproperties tableprop ON tableprop.id = sysobjects.id and tableprop.type = 3
left outer JOIN sysproperties.colprop ON colprop.id = syscolumns.id and colprop.type = 4
and colprop.smallid = syscolumns.colid
WHERE sysobjects.xtype in ('U','V')
Order by sysobjects.xtype, syscolumns.ColID,sysobjects.name
If you have any questions, or need any
assistance, please do not hesitate to Contact Us
|
