Link to home
Start Free TrialLog in
Avatar of malcolmbegg
malcolmbegg

asked on

Linking table/column comments in sysproperties

I have the following query:

SELECT     *
FROM         sysobjects INNER JOIN
                      sysproperties ON sysobjects.id = sysproperties.id
WHERE     (sysobjects.xtype = 'u') AND (sysproperties.name = 'MS_Description')

This (obviously) just returns all the fields from the joined tables where there is a description relating to a table.

Within this result set, there is a field that tells me what table the descriptions apply to (sysobjects.name).  This is fine for table descriptions (sysproperties.type = 3 - as far as I can tell!).  Column descriptions have sysproperties.type = 4, but I am not told which column the description applies to (I maybe am, but I don't know what to look for!).  I would like to know if these descriptions can be linked to a particular column so that I can pull out all the information with the following structure:

Table1/Table1Description
   T1Col1/T1Col1Description
   T1Col2/T1Col2Description
   T1Col3/T1Col3Description
   ...
Table2/Table2Description
   T2Col1/T2Col1Description
   T2Col2/T2Col2Description
   T2Col3/T2Col3Description
   ...

Many thanks,

Malcie.
ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arbert
arbert

You should also consider using the information_schema  views instead of querying the system tables directly.

Brett
Avatar of malcolmbegg

ASKER

Thanks to the comment from Hilaire, I have put together some pretty basic code that, when run in Query Analyzer, outputs a comma seperated list of desriptions for every column within every table.  This could be done more neatly (could put the data into the table, for example), but it meets my needs - I can copy the comma seperated list into Word and convert to a table.

Since this query is VERY rarely going to be run, I am not too bothered about querying system tables directly.

DECLARE @TableName sysname, @Id int, @TableValue varchar(8000)
DECLARE @ColumnName sysname, @ColumnValue varchar(8000)

DECLARE TableStats CURSOR STATIC LOCAL FOR
SELECT     sysobjects.name, sysobjects.id, CONVERT(varchar(8000), sysproperties.value)
FROM         sysobjects INNER JOIN
                      sysproperties ON sysobjects.id = sysproperties.id
WHERE     (sysobjects.xtype = 'u') AND (sysproperties.name = 'MS_Description') AND (sysproperties.type = 3)

OPEN TableStats

FETCH NEXT FROM TableStats
INTO @TableName, @Id, @TableValue

WHILE @@FETCH_STATUS = 0 BEGIN
      PRINT CONVERT(varchar(50), @Id) + ',T,' + @TableName + ',' + @TableValue

      DECLARE ColumnStats CURSOR STATIC LOCAL FOR
      SELECT     syscolumns.name, CONVERT(varchar(8000), sysproperties.value)
      FROM         syscolumns INNER JOIN
                            sysproperties ON syscolumns.id = sysproperties.id
                          and syscolumns.colid = sysproperties.smallid
                  INNER JOIN sysobjects ON sysobjects.id = sysproperties.id
      WHERE     (sysobjects.name = @TableName) AND (sysproperties.name = 'MS_Description')

      OPEN ColumnStats

      FETCH NEXT FROM ColumnStats
      INTO @ColumnName, @ColumnValue

      WHILE @@FETCH_STATUS = 0 BEGIN
            PRINT CONVERT(varchar(50), @Id) + ',C,' + @ColumnName + ',' + @ColumnValue
            
            FETCH NEXT FROM ColumnStats
            INTO @ColumnName, @ColumnValue
      END
      CLOSE ColumnStats
      DEALLOCATE ColumnStats
      
      FETCH NEXT FROM TableStats
      INTO @TableName, @Id, @TableValue
END
CLOSE TableStats
DEALLOCATE TableStats

Many thanks,

Malcie.
"Since this query is VERY rarely going to be run, I am not too bothered about querying system tables directly"

There was actually just a question posted yesterday  about a procedure that didn't work any more after a service pack was applied--system tables changed....
OK, something to look into.  My immediate problem is solved, but will look into a more robust, long-lasting solution using the information_schema views when I get time.  Thanks arbert.