4 Temmuz 2011 Pazartesi

Tsql ile bir serverdan diğerine extended property kopyalama

Copy Extended property from one SQL Server To another by tsql:




SELECT 
'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', 
@value=N'''+replace(
CAST(exprop.value AS VARCHAR(max))
,'''','"')
+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''
+tbl.name+''', @level2type=N''COLUMN'',@level2name=N'''+clmns.name +''';'

,tbl.name,
CAST(clmns.name AS VARCHAR(35)) ,exprop.value 

FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns
ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS idx
ON idx.object_id = clmns.object_id
AND 1 =idx.is_primary_key
LEFT OUTER JOIN sys.index_columns AS idxcol
ON idxcol.index_id = idx.index_id
AND idxcol.column_id = clmns.column_id
AND idxcol.object_id = clmns.object_id
AND 0 = idxcol.is_included_column
LEFT OUTER JOIN sys.types AS udt
ON udt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS typ
ON typ.user_type_id = clmns.system_type_id
AND typ.user_type_id = typ.system_type_id
LEFT JOIN sys.default_constraints AS cnstr
ON cnstr.object_id=clmns.default_object_id
LEFT OUTER JOIN sys.extended_properties exprop
ON exprop.major_id = clmns.object_id
AND exprop.minor_id = clmns.column_id
AND exprop.name = 'MS_Description'
WHERE (--tbl.name = @TableName and 
exprop.class = 1) --I don't wand to include comments on indexes
ORDER BY clmns.column_id ASC