Defining Extended Properties

SQL Server Magazine Web ExclusiveSeptember 15, 2005Section: Reader ChallengeInstantDoc ID 47621Title: Defining Extended Propertiesby Umachandar Jayachandran

Congratulations to Narasimhan Jayachandran, a senior DBA for First Data Resources in Omaha, Nebraska, who won first prize of $100 for the best solution to the September Reader Challenge, "Defining Extended Properties." Here's a recap of the problem and the solution to the September Reader Challenge.

Problem:

Michael is a database administrator in a company that hosts Web sites and databases. Every customer gets a separate database, and the databases are hosted on Microsoft SQL Server 2000. As part of the setup process for each database, Michael's company defines and stores some additional properties by using the extended properties feature. The properties can be string, integer, or date types. The following sample script shows some examples (from the Northwind and Pubs databases) of properties that Michael defines.

DECLARE @v sql_variant;EXEC northwind.dbo.sp_addextendedproperty N'Prop1', N'Value1'EXEC northwind.dbo.sp_addextendedproperty N'Prop2', 1SET @v = cast('2005-7-1' as smalldatetime);EXEC northwind.dbo.sp_addextendedproperty N'Prop3', @v;EXEC pubs.dbo.sp_addextendedproperty N'Prop1', N'Value2'EXEC pubs.dbo.sp_addextendedproperty N'Prop2', 2SET @v = cast('2005-7-2' as smalldatetime);EXEC pubs.dbo.sp_addextendedproperty N'Prop3', @v;

Michael needs to write a stored procedure that can search these extended properties and return the values. Help him do the following tasks:

1. Write a stored procedure to search the databases on a server for a specific property and value.
2. Be sure the stored procedure returns a list of the databases with the property name and value.
3. Specify string values as a pattern and search for it.
4. Search only for the specified value of other types.

Solution:

Michael can get the extended properties for a database by using the ::fn_listextendedproperty system function. The query below shows how to retrieve the properties defined for the Northwind database.

USE northwindGOSELECT ep.name, ep.valueFROM ::fn_listextendedproperty(default, default, default, default, default, default, default) AS epGO

The query below shows how to retrieve a specific property 'Prop1' and value like 'value1' or 'value2':

USE northwindGOSELECT ep.name, ep.valueFROM ::fn_listextendedproperty(default, default, default, default, default, default, default) AS epWHERE ep.name = 'Prop1' AND cast(ep.value as varchar(8000)) LIKE 'Value1'GO

In the query, the cast on Value column is required because it's a sql_variant data type and the column must be converted explicitly to varchar before using the LIKE operator. To get the exact value of an integer or datetime property, Michael can use a query like the following:

USE northwindGOSELECT ep.name, ep.valueFROM ::fn_listextendedproperty(default, default, default, default, default, default, default) AS epWHERE ep.name = 'Prop2' and ep.value = 1

or

SELECT ep.name, ep.valueFROM ::fn_listextendedproperty(default, default, default, default, default, default, default) AS epWHERE ep.name = 'Prop3' AND ep.value = cast('2005-7-1' AS datetime)GO

Then, Michael can use a cursor loop on master.dbo.sysdatabases and execute the query dynamically to get all the properties in each database. The predicate for the value column needs to be dynamic too, based on the data type of the search value. The stored procedure below shows how to search for a specific property and value and return the matching property and value with the database name:

— Getting list of databases based on extended property search.

CREATE procedure getextprops (@search_prop nvarchar(128), @search_value sql_variant)ASBEGINDECLARE @dbs cursor, @dbname nvarchar(128), @execsp nvarchar(255),@sql nvarchar(4000), @predicate nvarchar(1000), @basetype varchar(30), @datatype varchar(30)CREATE TABLE #mydbs ( dbname nvarchar(128) NOT NULL, name nvarchar(128) NOT NULL, value sql_variant NOT NULL )

— Set up cursor with the list of databases to search.

SET @dbs = cursor fast_forward FORSELECT d.nameFROM master.dbo.sysdatabases AS dWHERE d.name NOT IN ('master', 'model', 'msdb', 'tempdb')

— Determine the base type of the sql_variant parameter.

SET @basetype = cast(sql_variant_property(@search_prop, 'BaseType') AS varchar)

— Get the data-type declaration based on the base type.

SET @datatype = @basetype +CASEWHEN @basetype in ('varchar', 'char') THEN '(8000)'WHEN @basetype in ('nvarchar', 'nchar') THEN '(4000)'                        END

— Set up the predicate based on the base type.

IF @basetype IN ('varchar', 'char', 'nvarchar', 'nchar')            SET @predicate = N'cast(ep.value AS ' + @datatype + N') LIKE CAST(@value AS ' + @datatype + N')'ELSE            SET @predicate = N'ep.value = @value'

— Set up the SQL statement to execute dynamically.

SET @sql = N'                   SELCCT db_name(), ep.name, ep.value                   FROM ::fn_listextendedproperty(default, default, default, default, default, default, default) AS ep                   WHERE ep.objtype IS NULL AND ep.objname IS NULL AND ep.name = @prop AND ' + @predicate

— Loop through and run the dynamic SQL statement in each database.

OPEN @dbsWHILE(1=1)BEGINFETCH @dbs INTO @dbnameIF @@fetch_status — insert the values returned from the query into a temporary table:INSERT INTO #mydbs (dbname, name, value)EXEC @execsp @sql, N'@prop nvarchar(128), @value sql_variant', @prop = @search_prop, @value = @search_valueENDDEALLOCATE @dbs

— Retrieve all the matching values.

SELECT dbname, name, value FROM #mydbsENDGO

The batch below shows a sample of how you can use the stored procedure.

DECLARE @search_prop nvarchar(128), @search_value sql_variantSET @search_prop = N'Prop1'SET @search_value = N'Value[12]'EXEC getextprops @search_prop, @search_value

SET @search_prop = N'Prop2'SET @search_value = 2EXEC getextprops @search_prop, @search_value

SET @search_prop = N'Prop3'SET @search_value = cast('2005-7-1' as datetime)EXEC getextprops @search_prop, @search_value

OCTOBER READER CHALLENGE:

Now, test your SQL Server savvy in the October Reader Challenge, "Consolidating Data from Various Sources" (below). Submit your solution in an email message to [email protected] by September 15. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Gordon is the systems analyst in a company that sells software products. He is currently using SQL Server 2000 Service Pack 3 (SP3) to create a data-warehouse solution that consolidates information from various data sources in the company. Some of the data is customer information that comes from an IBM DB2 database. Below is the schema for the staging table containing the customer information with some sample data:

CREATE TABLE Customers_Work (    CustID nchar(8) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,    CustName varchar(100) NOT NULL)INSERT INTO Customers_Work (CustID, CustName) VALUES( N'cust0001', 'Customer #1')INSERT INTO Customers_Work (CustID, CustName) VALUES( N'Cust0001', 'Customer #2')INSERT INTO Customers_Work (CustID, CustName) VALUES( N'cust0002', 'Customer #3')INSERT INTO Customers_Work (CustID, CustName) VALUES( N'cust0003', 'Customer #4')INSERT INTO Customers_Work (CustID, CustName) VALUES( N'CUST0003', 'Customer #4')

Gordon is working on eliminating duplicates and other transformations before storing the data in the data warehouse. Help him do the following:

1. Modify the schema of the table to support searching the data in the CustID column in a case-insensitive manner-without modifying the original CustID column or the data it contains.
2. Allow efficient searches or a determination of duplicates based on the CustID column.
3. Be sure schema modifications are in addition to existing columns because this schema is used by other processes.