Adding description into the modify tables.

• Rini P

  SSC Enthusiast

  Points: 129

  More actions

  March 16, 2007 at 2:12 pm

  #369817

  When you click modify tables, usually it consist of 3 columns - Column Name, type and Allow nulls.  Does anybody know how to add description into the table modify instead of in the table designer in column property?  In this case, when you click modify tables, instead of consist 3 columns (column name, type, allow nulls), it will be consist 4 columns (Column Name, Type, allow Nulls, Description)?

• MohammedU

  SSCoach

  Points: 19644

  More actions

  March 17, 2007 at 6:47 pm

  #695593

  Are you looking for sp_addextendedproperty procedure?

  MohammedU
  Microsoft SQL Server MVP

• Rini P

  SSC Enthusiast

  Points: 129

  More actions

  March 19, 2007 at 8:20 am

  #695713

  I am affraid it's not what I am looking for.  What I am looking for is that when I modify the tables, you can see it contains 3 columns (Column Name, Data type, Allow nulls) as default column.

  Column Name	Data Type	Allow Nulls
  LastName	Varchar(20)
  FirstName	Varcahr(15)

  So, if I need to put the description for last name and firstname, I need to scrool down the cursor and put the description in the column property. 

  What I want is instead of scrolling down the cursor to the column property, how to add the property column into those 3 default column so I don't have to scroll up and down the cursor.  Similar like this,

  Column Name	Data Type	Allow Nulls	Description
  LastName	Varchar(20)		Last Name of the author
  FirstName	Varcahr(15)		First name and middle initial

  Is it possible to do so?

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323546

  More actions

  March 19, 2007 at 2:45 pm

  #695813

  the sp_addextendedproperty  Mohammed mentioned is the Description field you are talking about.

  From enterprise manager, no you cannot add columns tot eh design view of the tables....but you can select it in QA, or make your own application to display the information you are talking about...i guarantee it would be a bitch to create teh code to update from your own app, but you could certainly create  a display of that information:

  you'd just join sysobjects with syscolumns, and also grab the results of ::fn_listextendedproperty for the table/collumn in question

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323546

  More actions

  March 19, 2007 at 3:07 pm

  #695818

  here is an example:

  results:

  TableName	ColumnName	DataType	prec	ISNULLABLE	colid	VALUE
  T1	id	INT	10	NOT	NULL	1	Employee ID
  T1	name	CHAR	20	NOT	NULL	2	Employee Name

  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)

  select

  sysobjects.name As TableName,

  syscolumns.name As ColumnName,

  UPPER(TYPE_NAME(syscolumns.xtype)) AS DataType,

  syscolumns.prec,

  CASE WHEN syscolumns.isnullable=0 THEN '    NULL' ELSE 'NOT NULL' END AS ISNULLABLE,

  syscolumns.colid ,Y.VALUE

  from sysobjects

  inner join syscolumns on sysobjects.id=syscolumns.id

  inner join (SELECT   *

  FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)) Y

  on syscolumns.name=Y.objname

  where sysobjects.name='T1'

  order by syscolumns.colid

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Rini P

  SSC Enthusiast

  Points: 129

  More actions

  March 20, 2007 at 7:02 am

  #695906

  Mohammed and Lowell, thanks for your help.