June 27, 2005 at 3:20 am
I have a set of comments on table and its columns in oracle.
I need to put it in sql server.
Each time i need to go to Design table and place the description.
I would like to know do we have script of executing it.
or any alternative way.
Thanks in advance.
June 27, 2005 at 4:46 am
Check BOL, under syscomments. You can also include comments when you create/modify your tables in SQL statements in QA or enterprise manager. These comments are not compiled and do not add to the size of the compiled program (statement).
HTH Mike
June 27, 2005 at 4:49 am
Take a look at extended properties in Books Online. These are how Enterprise Manager manages the description property. The property should be called MS_Description to show up in Enterprise Manager.
June 27, 2005 at 5:38 am
Please see below example
sp_addextendedproperty 'caption', 'One digit identifier of system. See TimeSystem table for more information', 'user', dbo, 'table', 'Your Table Name Here', 'column', 'TimeSystemID'
GO
sp_addextendedproperty 'caption', 'up to 4 digit identifier of either store/warehouse/support building', 'user', dbo, 'table', 'Your Table Name Here', 'column', 'Location'
GO
sp_addextendedproperty 'caption', 'date of the punches/absences', 'user', dbo, 'table', 'Your Table Name Here', 'column', 'RecordDate'
GO
sp_addextendedproperty 'caption', '''P''unch or ''A''bsence', 'user', dbo, 'table', 'Your Table Name Here', 'column', 'RecordType'
GO
sp_addextendedproperty 'caption', '1 for record to load, 0 for no records found/to load', 'user', dbo, 'table', 'Your Table Name Here', 'column', 'NumRecords'
GO
sp_addextendedproperty 'caption', 'This is to be used for restartability to avoid redoing same portions of updates', 'user', dbo, 'table', 'Your Table Name Here', 'column', 'ProcessedDtTm'
GO
SELECT SO.[name], colid, SC.[name], ISNULL(SP.[value], 'Not provided') [Column Description]
FROM sysobjects SO
INNER JOIN syscolumns SC
ON SO.[id] = SC.[id]
LEFT JOIN sysproperties SP
ON SO.[id] = SP.[id]
AND SC.colid = SP.smallid
WHERE SO.[name] = 'Your Table Name Here'
ORDER BY colid
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 27, 2005 at 6:01 am
To list extended properties you can use fn_listextendedproperty instead of querying system tables though. And like I mentioned above, if you want to see them in EM then the special name MS_Description should be used.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply