Do Extended Properties show up in a User Interface anywhere 1

[SBendBuckeye]

Hello all,

We are upgrading a system and are trying to come up with the best way to flag obsolete colunms (eg all old int identity primary keys will be changed to guid primary keys, etc). We want to flag them so we can delete them later after all of the views and stored procedures have been changed.

I would like to use an extended property, but I don't know where to see them. Alternatively, we have talked about adding Obsolete to the Description column so at least you see it when you open a table in Design mode in Enterprise Manager.

Does 2005 help in this regard or am I missing anything obvious here? Thanks in advance for any ideas and/or suggestions!

Have a great day!

j2consulting@yahoo.com

 

[SQLDenis]

extended properties can be added with the sp_addextendedproperty procedure (use caption)

to see the value use the FN_LISTEXTENDEDPROPERTY function

examples
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)




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SBendBuckeye]

Thanks SqlDenis, I knew about both of those methods but was hoping they actually show up somewhere in Enterprise Manager or one of the other tools.

Have a great day!

j2consulting@yahoo.com

 

[SQLDenis]

In enterprise manager
right click on a table select design table
right above default value there is a description field




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Also from QA
DB-->Tables-->columns right click on a column and select extended properties

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SBendBuckeye]

Thanks SQLDenis! Enjoy your star. Your last post about seeing it in QA is the piece I was missing. Thanks again!

Lets say I added an extended property named Obsolete. Is there a way to list all columns in all tables that have that property set? Everything that I have seen in BOL seems to indicate that fn_listextendedproperty needs to work on 1 table at a time for column information.

Have a great day!

j2consulting@yahoo.com

 

[SQLDenis]

According to BOL this should do it but it doesn't work for me

SELECT *
FROM ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)

-Or-

SELECT *
FROM ::fn_listextendedproperty(default, default, default, default, default, default, default)



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

SBendBuckeye , here it is

declare @chvPropertyName varchar(50)
select @chvPropertyName ='caption'
--if you need all properties comment out the statement after the where clause (and e.[name] =@chvPropertyName)
create table #ExtendedProperties (
 table_id int NULL,
 table_name nvarchar(128) NULL,
 column_order int NULL,
 column_name varchar(60) NULL,
 column_datatype varchar(20) NULL,
 column_length int NULL,
  property_name varchar(20) NULL,
 column_description varchar(500) NULL
)

DECLARE @table_name nvarchar(128)

DECLARE tablenames_cursor CURSOR FOR 
SELECT name FROM sysobjects where type = 'U' and status > 1 order by name

OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
 insert #ExtendedProperties select 
  o.[id] as 'table_id', 
  o.[name] as 'table_name',
  c.colorder as 'column_order',
  c.[name] as 'column_name',
  t.[name] as 'column_datatype',
  c.[length] as 'column_length',
  e.[name] as PropertyName,
  Cast(e.value as varchar(500)) as 'column_description' 
 from sysobjects o inner join syscolumns c on o.id = c.id inner join systypes t on c.xtype = t.xtype
  join ::FN_LISTEXTENDEDPROPERTY(NULL,
 N'user',N'dbo',N'table', @table_name, N'column', null) e on c.name = e.objname
 where o.name = @table_name
and e.[name] =@chvPropertyName -- comment this out if you need all properties
 order by c.colorder



   FETCH NEXT FROM tablenames_cursor INTO @table_name
END

CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor

select * from #ExtendedProperties
drop table #ExtendedProperties


Go

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/