Fields description: how to manage

Category: sql server documentation


Emanuele Lanza on Mon, 22 Jun 2015 07:54:42

Hello everyone.

I have a problem with the extended properties, which I use for a minimum of internal documentation to the db. SQL Server doesn't seem improved from this point of view, even using the 2012 version: Access was more easily documentable!

In any case I'm supported from RedGate SQL Doc for the semi-automatic creation of reports and this allows me to change more or less a description of views fields.

However I work on a data import database using SELECT INTO to create elaborate tables daily.

I would need then to recopy the extended properties for columns descritption from the source views to destination tables, as simple as possible.
Someone has already experienced about this?
Thank you very much


Stan210 on Tue, 23 Jun 2015 10:28:24

wen you use select * into  - there are many properties that are not copied to the new destination table such as primary key, foreign key, indexes etc. to me those are more concern than extended properties, neverthless they are important.

If possible, i would suggest to script the source table and recreate it as different name.

but if you do not want to do, you can try this method, you will need to come up with a way to script it but is possible to do this.

sys.fn_listextendedproperty - function will list out the extended properties, so, you can get them using this.

try this example:

CREATE TABLE [dbo].[test](
	[sno] [int] NULL


EXEC sys.sp_addextendedproperty @name=N'test123', @value=N'this is a test' , @level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'sno'

 select *
 from sys.fn_listextendedproperty(null,'schema','dbo','table','test','column',default)

--now the information from the above function, you can build the procedure below on the new table

EXEC sys.sp_addextendedproperty @name=N'test123', @value=N'this is a test' , @level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'testNEWTAble', @level2type=N'COLUMN',@level2name=N'sno'

Hope it Helps!!