SQL script for updating SQL Extended Properties with a Cursor

Question

I have a task of updating the tables in a database which are missing Extended Properties for 'Category' and 'Description'. The script below is what I am currently trying to implement, but it doesn't function as I'd hoped it would.

The first part of the script successfully returns the tables which have NULL or missing values for 'Category' and 'Description'. These results are used by a cursor to iterate through each table so I can perform an operation on them. For each table in the cursor, I want to retrieve the correct values from TemporaryTable which contains the correct 'Category' and 'Description'. Then, pass those correct values to the EXEC statements to update or create the Extended Property. The only thing that doesn't seem to work is that the variables 'categ' and 'descr' don't get assigned the values in the second SELECT statement where I expected them to.

I've tried as many combinations as I can think of to get these values into the variables, but none seem to work for me. Hopefully somebody can point out where I am going wrong.

Thanks in advance for your suggestions.

    Few variables declared for use in this script
*/
DECLARE @tablename VARCHAR(100)
DECLARE @categ VARCHAR(50)
DECLARE @descr VARCHAR(MAX)

DECLARE myCursor CURSOR FOR

/*
    This SELECT/FROM returns all table names which have
    a NULL value for either category or description. This statement correctly
    returns the subset of tables.
*/
    SELECT
        t.name AS tablename
    FROM sys.tables t
        LEFT JOIN sys.extended_properties AS description
            ON t.object_id = description.major_id
            AND description.minor_id = 0
            AND description.name = 'MS_Description'
        LEFT JOIN sys.extended_properties AS category
            ON t.object_id = category.major_id
            AND category.minor_id = 0
            AND category.name = 'Table_Category'
        WHERE type = 'U'
        AND (category.value IS NULL OR description.value IS NULL)
        AND t.name NOT LIKE 'temp%'
        AND t.name NOT LIKE 'tmp%'
        AND t.name NOT LIKE '%_BAK'

OPEN myCursor
FETCH NEXT FROM myCursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

/*
    This SELECT/FROM/WHERE takes information (correct extended properties) about all
    cursor tables from a temporary table.
    The purpose is to update the NULL (or not present) extended properties in the subset of tables
    of the cursor.
    The [Table Name], tp.Category, and tp.Description are correctly output in the results, but the assignment
    to the variables categ, and descr (which I want to use in the EXEC statement for updating properties)
    doesn't seem to happen.
*/
    SELECT
        tp.[Table Name],
        tp.Category AS categ,
        tp.Description AS descr

    FROM TemporaryTable tp
    WHERE @tablename = tp.[Table Name]

--If category ext_prop doesn't exist, add it
    IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(@tablename) AND [name] = N'Table_Category' AND [minor_id] = 0)
        EXEC sys.sp_addextendedproperty @name=N'Table_Category', @value=N@categ , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N@tablename
--Else, update extended property
    ELSE
        EXEC sys.sp_updateextendedproperty @name=N'Table_Category', @value=N@categ , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N@tablename

--If description ext_prop doesn't exist, add extended property
    IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(@tablename) AND [name] = N'MS_Description' AND [minor_id] = 0)
        EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N@desc , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N@tablename
--If Not Exists, add extended property
    ELSE
        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N@desc , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N@tablename

    PRINT @categ
    FETCH NEXT FROM myCursor INTO @tablename
END
CLOSE myCursor
DEALLOCATE myCursor

score 0 · Answer 1 · answered Jul 20 '22 at 15:33

0

I don't see any trace of variable assignments, just aliases. To assign a value to a variable you must either use

SET @varname = value or query

or you can do it using a select statement like

    SELECT
        @categ = tp.Category,
        @descr = tp.Description
    FROM TemporaryTable tp
    WHERE @tablename = tp.[Table Name]

answered Jul 20 '22 at 15:33

Giovanni Luisotto

1,382
7
12

Using your second suggestion brought me closest to the solution. I implemented it as you described, but moved my variable declarations to just before the SELECT statement. After these two changes, it ran successfully. Thanks – Conor Keating Jul 21 '22 at 16:40
do you mind marking it as the solution? – Giovanni Luisotto Jul 22 '22 at 08:42

SQL script for updating SQL Extended Properties with a Cursor

1 Answers1