Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Conclusions usually happen when we're tired of thinking
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

03
February

How Do I List All the Descriptions of Tables, Views and Fields of a SQL Database?

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

MAKE A COMMENT

Name *

Email Address *

Comment *