May 11, 2012 at 7:38 am
Hello
My problem is the following:
I have a table (table A) that has several columns and each one of those columns has a description on the description property. I did that by using sql server management studio GUI.
I have another table (Table B) which is the same as table A but with no descriptions in the description property. I want to update Table B with descriptions from table A by using code and not copying and pasting the values using the Gui. I 've searched in google and i found that there is a system view sys.extended_properties which keeps the values of the descriptions of each table.The problem is that sql server 2008 does not allow you to update the system view.
Is there another way by using code to update description values for table B from table A?
May 11, 2012 at 8:34 am
apostolis.karayiannis (5/11/2012)
HelloMy problem is the following:
I have a table (table A) that has several columns and each one of those columns has a description on the description property. I did that by using sql server management studio GUI.
I have another table (Table B) which is the same as table A but with no descriptions in the description property. I want to update Table B with descriptions from table A by using code and not copying and pasting the values using the Gui. I 've searched in google and i found that there is a system view sys.extended_properties which keeps the values of the descriptions of each table.The problem is that sql server 2008 does not allow you to update the system view.
Is there another way by using code to update description values for table B from table A?
have a look at the stored proc
sp_addextendedproperty
MVDBA
May 11, 2012 at 3:20 pm
Yes, that can be done.
It's easier with linked servers but possible even without.
Do you have a Linked Server defined between the servers? Or do you prefer not to have a linked server on either server?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 12, 2012 at 10:00 am
apostolis.karayiannis (5/11/2012)
HelloMy problem is the following:
I have a table (table A) that has several columns and each one of those columns has a description on the description property. I did that by using sql server management studio GUI.
I have another table (Table B) which is the same as table A but with no descriptions in the description property. I want to update Table B with descriptions from table A by using code and not copying and pasting the values using the Gui. I 've searched in google and i found that there is a system view sys.extended_properties which keeps the values of the descriptions of each table.The problem is that sql server 2008 does not allow you to update the system view.
Is there another way by using code to update description values for table B from table A?
If you change the scripting options in SSMS (under Tools/Options) to include "Extended Properties", you can just script out the table and all of the extended properties will script out, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2012 at 1:26 am
Thank you for your answers
May 15, 2012 at 6:26 am
If it helps, here is a script that will create a script to set all values for documentation for the columns for all tables in a database. Just set the database context and run it. The Messages results tab will hold the results. Just cut and paste them to a new query window.
I use CLMNDOC, but you can use any description that you want to. Just change in the code.
It pulls out existing values, so you could run it on the database with existing info, create the script, then run that script on the database without the values.
/* ============================================================================================================= */
/* Generate Column Documentation Settings SCRIPT GENERIC VERSION */
/* ============================================================================================================= */
/*
NOTE: Remember to set the database context before running this script.
*/
DECLARE @includeviewsbit
DECLARE @spstatint
DECLARE @errmsgvarchar(200)
DECLARE @recnint
DECLARE @numrecs int
DECLARE @SNvarchar(50)
DECLARE @TN varchar(50)
DECLARE @de varchar(50)
DECLARE @tt varchar(50)
DECLARE @currsnvarchar(50)
DECLARE @currtnvarchar(50)
DECLARE @ev varchar(1000)
DECLARE @servname varchar(128)
DECLARE @dbname varchar(128)
DECLARE @currdt varchar(30)
DECLARE @currdtlen int
DECLARE @servnamelen int
DECLARE @dbnamelen int
DECLARE @linelen int
DECLARE @vl varchar(2000)
DECLARE @vl2 varchar(2000)
DECLARE @setasnew bit
SET NOCOUNT ON
/* ============================================================================================================ */
/* User Can Set These Variables */
SET @includeviews = 0-- Set whether to include views 1=TRUE 0=FALSE
SET @setasnew = 1-- whether to do all settings as if from scratch, like re-creating the database and then using these settings
/* ============================================================================================================ */
SET @linelen = 130
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
/* ======================================================================================================== */
SELECT @currdt = CONVERT(varchar(30),GETDATE(),113)
SELECT @servname=@@servername
SELECT TOP 1 @dbname=TABLE_CATALOG FROM INFORMATION_SCHEMA.COLUMNS
SET @currdtlen = LEN(@currdt)
SET @servnamelen = LEN(@servname)
SET @dbnamelen = LEN(@dbname)
PRINT '/* ' + REPLICATE('=',@linelen) + ' */'
PRINT '/* ' + REPLICATE(' ',20) + 'Documentation for Server: ' + @servname + REPLICATE(' ',15) + 'Database: ' + @dbname + REPLICATE(' ',@linelen-20-27-@servnamelen-15-11-@dbnamelen) + ' */'
PRINT '/* ' + REPLICATE(' ',20) + 'For Date: ' + @currdt + REPLICATE(' ',@linelen-20-11-@currdtlen) + ' */'
PRINT '/* ' + REPLICATE('=',@linelen) + ' */'
PRINT ''
PRINT ''
PRINT 'USE ' + @dbname
PRINT 'GO'
PRINT ''
PRINT '/* ' + REPLICATE(' ',30) + 'Column Definitions' + REPLICATE(' ',30) + ' */'
PRINT ' '
PRINT ' '
SELECT TABLE_SCHEMA AS SchemaName2, TABLE_NAME AS TableName2, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN CONVERT(varchar(100),'TABLE')
WHEN 'VIEW' THEN CONVERT(varchar(100),'VIEW')
END as TableType2
INTO #tabledoc
FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'
ORDER BY SchemaName2, TableName2
SELECT TABLE_SCHEMA AS 'SchemaName',
TABLE_NAME AS 'TableName',
COLUMN_NAME AS 'DataElement',
ORDINAL_POSITION AS 'OrdinalPosition',
CONVERT(varchar(50),'') AS TableType
INTO #columndoc
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY SchemaName, TableName, ORDINAL_POSITION
UPDATE #columndoc
SET TableType = (SELECT TableType2 FROM #tabledoc WHERE SchemaName=SchemaName2 AND TableName=TableName2)
if @includeviews = 0
DELETE FROM #columndoc WHERE TableType='VIEW'
DECLARE backupFiles CURSOR FOR
SELECT SchemaName, TableName, DataElement, TableType
FROM #columndoc
ORDER BY SchemaName, TableName, OrdinalPosition
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de, @tt
--set the initial values so will print the heading
SET @currsn = ''
SET @currtn = ''
WHILE @@FETCH_STATUS = 0
BEGIN
--do the heading for a new table
if @SN <> @currsn or @TN <> @currtn
BEGIN
PRINT '/* ================================================================================================================================ */'
PRINT '/* Documentation for Schema: ' + @SN + ' Table: ' + @TN + ' Table Type: ' + @tt + ' */'
PRINT ''
if @SN <> @currsn
SET @currsn = @SN
if @TN <> @currtn
SET @currtn = @TN
END
PRINT '/* ======= Schema: ' + @SN + ' ========= Table: ' + @TN + ' ======= Column: ' + @de + ' ======== */'
PRINT ''
SET @vl = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('CLMNDOC',
'Schema', @SN,
'Table', @TN,
'Column', @de)),'')
-- set the comment for a known column name
-- Replace single quote with double to make work
SET @vl2 = REPLACE(@vl,'''','''''')
PRINT '--Setting the description of the column (how is it used, what data does it hold)'
if @setasnew=1
BEGIN
if @vl = ''
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNDOC'', @value = '''','
else
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNDOC'', @value = ''' + @vl2 + ''','
END
else
BEGIN
if @vl = ''
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNDOC'', @value = '''','
else
PRINT 'EXEC sp_updateextendedproperty @name = ''CLMNDOC'', @value = ''' + @vl2 + ''','
END
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Table'', @level1name = ''' + @TN + ''','
PRINT '@level2type = ''Column'', @level2name = ''' + @de + ''''
PRINT 'GO'
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de, @tt
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #tabledoc
DROP TABLE #columndoc
/* ======================================================================================================== */
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@Messagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
PRINT 'ERROR OCCURED: ' + @Message
END CATCH
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply