| Groups | Blog | Home |
sql server new users : How can I attach a description to a table
You can use Enterprise Manager in the table designer or you can simply use
the sp called sp_addextendedproperty. DECLARE @v sql_variant SET @v = N'This is my Description' EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'MyTable', NULL, NULL -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Johan" <Johan@discussions.microsoft.com> wrote in message news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... > You can easily enter a description at a table field. > > But how can I enter a description for the table itself?
[quoted text, click to view]
"Johan" <Johan@discussions.microsoft.com> wrote in message news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... > You can easily enter a description at a table field. > > But how can I enter a description for the table itself? Why would you do that in the database? Better to put it in a data dictionary or logical design document instead IMO. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx --
My experience is that developers/dba's don't like updating models/documents.
So it is difficult to maintain good documentation. When the description is directly attached to the table, it is easier to maintain and you can create documentation automatically via the system tables. Regards, Johan Theunissen [quoted text, click to view] "David Portas" wrote:
> "Johan" <Johan@discussions.microsoft.com> wrote in message > news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... > > You can easily enter a description at a table field. > > > > But how can I enter a description for the table itself? > > Why would you do that in the database? Better to put it in a data dictionary > or logical design document instead IMO. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > >
When I execute this sp, it works "(1 row(s) affected)".
But where is the descripstion stored? [quoted text, click to view] "Andrew J. Kelly" wrote:
> You can use Enterprise Manager in the table designer or you can simply use > the sp called sp_addextendedproperty. > > DECLARE @v sql_variant > SET @v = N'This is my Description' > EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', > N'table', N'MyTable', NULL, NULL > > > -- > Andrew J. Kelly SQL MVP > > > "Johan" <Johan@discussions.microsoft.com> wrote in message > news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... > > You can easily enter a description at a table field. > > > > But how can I enter a description for the table itself? > >
What does the N' mean in front of the parameters?
[quoted text, click to view] "Andrew J. Kelly" wrote:
> You can use Enterprise Manager in the table designer or you can simply use > the sp called sp_addextendedproperty. > > DECLARE @v sql_variant > SET @v = N'This is my Description' > EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', > N'table', N'MyTable', NULL, NULL > > > -- > Andrew J. Kelly SQL MVP > > > "Johan" <Johan@discussions.microsoft.com> wrote in message > news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... > > You can easily enter a description at a table field. > > > > But how can I enter a description for the table itself? > >
The description is stored in sysproperties.
The N in front of a string indicates it's a unicode string. -Sue On Tue, 17 Jan 2006 04:25:03 -0800, "Johan" [quoted text, click to view] <Johan@discussions.microsoft.com> wrote:
>What does the N' mean in front of the parameters? > >"Andrew J. Kelly" wrote: > >> You can use Enterprise Manager in the table designer or you can simply use >> the sp called sp_addextendedproperty. >> >> DECLARE @v sql_variant >> SET @v = N'This is my Description' >> EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', >> N'table', N'MyTable', NULL, NULL >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Johan" <Johan@discussions.microsoft.com> wrote in message >> news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... >> > You can easily enter a description at a table field. >> > >> > But how can I enter a description for the table itself? >> >> >>
It is stored internal to the db in system tables.
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "Johan" <Johan@discussions.microsoft.com> wrote in message news:4F6D6A1C-5EDC-4FAA-8947-FE41363AB16B@microsoft.com... > When I execute this sp, it works "(1 row(s) affected)". > > But where is the descripstion stored? > > "Andrew J. Kelly" wrote: > >> You can use Enterprise Manager in the table designer or you can simply >> use >> the sp called sp_addextendedproperty. >> >> DECLARE @v sql_variant >> SET @v = N'This is my Description' >> EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', >> N'table', N'MyTable', NULL, NULL >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Johan" <Johan@discussions.microsoft.com> wrote in message >> news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... >> > You can easily enter a description at a table field. >> > >> > But how can I enter a description for the table itself? >> >> >>
When I run the sp, it generates the following values in tblproperties
field id = number corresponding to the id of the sysobjects table smallid = 0 (what is this?) type = 3 (what is this?) name = name in sp value = <Binary> (Why is this not the description string?) [quoted text, click to view] "Sue Hoegemeier" wrote:
> The description is stored in sysproperties. > The N in front of a string indicates it's a unicode string. > > -Sue > > On Tue, 17 Jan 2006 04:25:03 -0800, "Johan" > <Johan@discussions.microsoft.com> wrote: > > >What does the N' mean in front of the parameters? > > > >"Andrew J. Kelly" wrote: > > > >> You can use Enterprise Manager in the table designer or you can simply use > >> the sp called sp_addextendedproperty. > >> > >> DECLARE @v sql_variant > >> SET @v = N'This is my Description' > >> EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', > >> N'table', N'MyTable', NULL, NULL > >> > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Johan" <Johan@discussions.microsoft.com> wrote in message > >> news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... > >> > You can easily enter a description at a table field. > >> > > >> > But how can I enter a description for the table itself? > >> > >> > >> >
It's an undocumented system table so it shouldn't really
matter. id is the first column, not field id. It's the id for the table. Value is the description string stored in binary format. I can't remember the others off the top of my head - you can look at the system stored procedure sp_validatepropertyinuput to figure those out. I'm not sure why it would matter though. -Sue On Tue, 17 Jan 2006 23:37:05 -0800, "Johan" [quoted text, click to view] <Johan@discussions.microsoft.com> wrote:
>When I run the sp, it generates the following values in tblproperties > >field id = number corresponding to the id of the sysobjects table >smallid = 0 (what is this?) >type = 3 (what is this?) >name = name in sp >value = <Binary> (Why is this not the description string?) > > > >"Sue Hoegemeier" wrote: > >> The description is stored in sysproperties. >> The N in front of a string indicates it's a unicode string. >> >> -Sue >> >> On Tue, 17 Jan 2006 04:25:03 -0800, "Johan" >> <Johan@discussions.microsoft.com> wrote: >> >> >What does the N' mean in front of the parameters? >> > >> >"Andrew J. Kelly" wrote: >> > >> >> You can use Enterprise Manager in the table designer or you can simply use >> >> the sp called sp_addextendedproperty. >> >> >> >> DECLARE @v sql_variant >> >> SET @v = N'This is my Description' >> >> EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', >> >> N'table', N'MyTable', NULL, NULL >> >> >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Johan" <Johan@discussions.microsoft.com> wrote in message >> >> news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... >> >> > You can easily enter a description at a table field. >> >> > >> >> > But how can I enter a description for the table itself? >> >> >> >> >> >> >> >>
If you are just trying to return the extended properties on an object, then
you should use the documented method for doing so. In SQL Server 2000, the correct method is the fn_listextendedproperty function. Here's a little example that illustrates how to use it. CREATE table T1 (id int , name char (20)) EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default) If you're using SQL Server 2005, use the sys.extended_properties catalog view instead. It's easier to use than the function. Search Books Online for extended properties for details. -- Gail Erickson [MS] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights [quoted text, click to view] "Johan" <Johan@discussions.microsoft.com> wrote in message news:FB81C6DB-875F-406C-AF67-C071C24B4BD7@microsoft.com... > When I run the sp, it generates the following values in tblproperties > > field id = number corresponding to the id of the sysobjects table > smallid = 0 (what is this?) > type = 3 (what is this?) > name = name in sp > value = <Binary> (Why is this not the description string?) > > > > "Sue Hoegemeier" wrote: > >> The description is stored in sysproperties. >> The N in front of a string indicates it's a unicode string. >> >> -Sue >> >> On Tue, 17 Jan 2006 04:25:03 -0800, "Johan" >> <Johan@discussions.microsoft.com> wrote: >> >> >What does the N' mean in front of the parameters? >> > >> >"Andrew J. Kelly" wrote: >> > >> >> You can use Enterprise Manager in the table designer or you can simply >> >> use >> >> the sp called sp_addextendedproperty. >> >> >> >> DECLARE @v sql_variant >> >> SET @v = N'This is my Description' >> >> EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', >> >> N'table', N'MyTable', NULL, NULL >> >> >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Johan" <Johan@discussions.microsoft.com> wrote in message >> >> news:F7762E97-A5F8-4A26-9A14-B0D06964D667@microsoft.com... >> >> > You can easily enter a description at a table field. >> >> > >> >> > But how can I enter a description for the table itself? >> >> >> >> >> >> >> >>
[quoted text, click to view] Johan wrote: > You can easily enter a description at a table field. > > But how can I enter a description for the table itself? SchemaToDoc (http://www.schematodoc.com) has as part of its interface a way to easily view and enter descriptions for all of the tables and fields in your database. These descriptions get stored as extended properties in your database. SchemaToDoc can then produce a Word document that documents the structure of your database and include in that document the table and field descriptions that you entered.
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |