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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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....
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....
ASKER
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.
Brett