Add Extended property to every column in every table...

Question

Post reply

Add Extended property to every column in every table...

Dave Rooke

SSC Eights!

Points: 906
More actions
March 28, 2011 at 4:01 am

#147622

i am using the sp_addextendedproperty to add a "Help" property to columns, but i now have a database which has over 100 tables and approx 30 columns in each.

could someone advise how i could run this so that it will add this extended property to every column in every table?

many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

have you put together a spreadsheet that has the description you want to add yet?

you can create a cell calculation in excel to generate the required statements, or do it in TSQL if you can select the data.

its pretty simple to generate the required scripts:



WITH myCTE AS

(

SELECT 'dbo' as Schemaname,'VADDR' As  Tablename,'ADDRID' As ColumnName, 'Unique PK of Table' As Descrip UNION ALL

SELECT 'dbo','VADDR','ADDR1', 'First Address Line' UNION ALL

SELECT 'dbo','VADDR','ADDR2', 'Second Address Line'  UNION ALL

SELECT 'dbo','VADDR','CITY', 'city name'  UNION ALL

SELECT 'dbo','VADDR','STATECODE', 'two char state code'  UNION ALL

SELECT 'dbo','VADDR','ZIPCODE', '9 digit zip plus dashes or formatting'

) 

select

         'EXEC sys.sp_addextendedproperty

          @name = N''' + 'Help' + ''', @value = N''' + REPLACE(convert(varchar(max),[Descrip]),'''','''''') + ''',

          @level0type = N''SCHEMA'', @level0name = [' + Schemaname + '],

          @level1type = N''TABLE'', @level1name = [' + Tablename + '],

          @level2type = N''COLUMN'', @level2name = [' + ColumnName + '];'

 from myCTE

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!

Dave Rooke SSC Eights! Points: 906 More actions · Answer 2

thanks for that....

at the moment i just need to create the extended property to eventually hold help information on that column as i have a front end to allow us to input the relevant information for each column..

with your script is there any way to loop through all tables/columns without declaring them as such?

cheers

dave

Lowell SSC Guru Points: 323482 More actions · Answer 3

you want to create empty extended properties?

instead of the select i used, you could use sys.tables and sys.columns this to generate the commands:



WITH myCTE AS

(

 SELECT

   SCHEMA_NAME(schema_id) as Schemaname,

   objz.name as TableName,

   colz.name as ColumnName,

   '' as Descrip

   from sys.tables objz inner join sys.columns colz on objz.object_id=	colz.object_id

) 

select

         'EXEC sys.sp_addextendedproperty

          @name = N''' + Tablename + ''', @value = N''' + REPLACE(convert(varchar(max),[Descrip]),'''','''''') + ''',

          @level0type = N''SCHEMA'', @level0name = [' + Schemaname + '],

          @level1type = N''TABLE'', @level1name = [' + Tablename + '],

          @level2type = N''COLUMN'', @level2name = [' + ColumnName + '];'

 from myCTE

i would strongly recommend do it a little differently: in our shop we give our tech writer the schema/tablename/columnname, he fills in the descriptions, and then we put them in the database after he's provided us with the needed descriptions;

unless you are using this to generate tooltips for field names, I don't see any advantage to putting in blank descriptions

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!