Andy DBA (1/11/2007)
CORRECTION. The prior posted sql may have duplicates. The following should work better:
--List tables, columns and column descriptions
select SO.name as 'table', SC.name as field,
ST.name as datatype, SC.length as 'size',
sp.value as 'description'
from syscolumns SC inner join
sysobjects SO on SC.id = SO.id inner join
systypes ST on ST.xusertype = SC.xusertype left join
sysproperties sp on sp.id = so.id and sp.smallid = SC.colid
and sp.name = 'MS_Description'
where SO.xtype = 'U' and SO.status > 0
order by SO.name, SC.name
When I try to run the query I get an error
"Msg 208, Level 16, State 1, Line 2 Invalid object name 'sysproperties'."
If i remove the sysproroerties table from the query the query will run, for some reason its the only table that I can't find.
Furthermore I logged in as SA to see if it was a permissions error, but I still got the same error.
Next question (could be linked to the problem above), if i wanted to browse these tables, how would I access them?
My first guess, using the SQL SMS would be to go
DB -> Tables -> System Tables -> sysproperties
Thanks in advance
Kris
However the only table in "System Tables" is sysdiagrams