June 15, 2002 at 11:56 pm
Hi All,
I'm new to this forum and hope I am posting this in the right place.
I am in a delema.... I need to get the column and table descriptions for our entire DB. Is there some code or (even better) software that will help with my documentation issues.
I have tried:
sp_help
fn_listextendedproperty
and some code I foudn on this site.
None of them seem to be working 🙁
Any advice would be greatly appreciated.
Thanks
E
June 16, 2002 at 8:21 am
You can be better off generating scripts for your entire db this will give you description of every object including your sp's too.
June 16, 2002 at 8:27 am
The easiest and fastest way is to use Enterprise Manager. Drill to the database and right click it. Then find script database and you can select the various items you want to script. Also you can script into one large file or a file for each object (which I prefer). This is for the DDL descriptions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 16, 2002 at 10:45 am
If you're trying to retrieve description info that was added to the db schema, you'll find it in sysproperties.
Andy
June 16, 2002 at 7:06 pm
Hi,
Thankyou for the replies.
Last time I generated the SQL script I didn't scroll down far enough to see the descriptions.
Also, Andy - I had looked at sysproperties previously, however wasn't sure how to view the value field (it only showed up as <binary>) - on using the Select value from.... I was able to see the actual descriptions. However, there are no coresponding column or table names.
Is there a way I can view the descriptions & table and column names??? (I am currently reformating the output of the SQL script to get this information).
Any ideas why fn_listextendedproperties hasn't worked for me?? I initially thought that maybe the descriptions were not 'extended properties', however on viewing the output from the script they are listed as extended properties.
Thanks Again.
E
June 16, 2002 at 7:47 pm
Sorry no server in front of me but one of those fields is an ID field for the parent object attached to. Usually doing object_name(thatfield) will tell where from. Otherwise you may have to tie back to either the sysobjects table of the current database or the sysdatabases in master.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 16, 2002 at 8:54 pm
Thankyou again.
I have come across an intersting situation...
::fn_listextendedproperty is now working, however I can only get the information for one table at a time.
select objname, value
from ::fn_listextendedproperty(NULL,'user','dbo','table','tblCMPCompany','column',default)
returns the column name and description for that particular table - but I want to get the column name and description for all tables - without having to run this function 100's of times.
Going by the documentation ""If the value for property_name is NULL or default, fn_listextendedproperty returns all the properties for the object."" It appears that using Null or Default for the table will return all table, but this is not the case.
Any ideas??
Thanks
Emma
June 17, 2002 at 5:44 am
I have only played with the function a bit but could rarely get it to work. This may help a bit though
select object_name(p.id) as Object, c.[name], p.[name] as type, value from sysproperties p inner join syscolumns c on colid = smallid and p.id = c.id
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply