Question
Emanuele Lanza on Mon, 22 Jun 2015 07:54:42
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.
Replies
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 ) ON [PRIMARY] GO 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' GO 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' GO
Hope it Helps!!