Posted - Feb 09 2012 : 1:00:21 PM
I've recently switched to storing our data models in Visual Studio 2010 database projects, and versus using the data modelling software to associate tables to their logical subject areas. One way to implement these assocations in a VS database project is to implement them as extended properties.
Extended properties can be applied to a number of SQL server objects. One common use is to store the table definitions that you can type in from the Table propertiess dialog in SSMS; the definitions creaeted within SSMS are recorded by a property named 'MS Description'. You can create your own properties to describe other attributes. In my case, I create an extended property for each subject area that a table belongs to. For example, to indicate that the [RetailProduct] table belongs to the subject area called "Sales - POS Product Catalog Retail Product", this statement is used:
@name= 'IDI_MODEL.07.650' /* <-- this is my code name for the model */
, @value= 'Sales - POS Retail Product'
, @level0type= 'SCHEMA'
, @level0name= 'dbo'
, @level1type= 'TABLE'
, @level1name= 'RetailProduct'
Once you've created simialar properties for all of the subject areas and tables, it didn't take too long to use some of SQL's XML features to automatically generate the "models" section of the .ssproj file.
The base query to extract a list of the extended properties on table objects looks like this:
select * from ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL)
For my query, i wanted to constrain that list to match my naming scheme for the models (name like 'IDI_MODEL%').
For the XML structure, each model model would be listed, and then within each model, the list of tables associated with it would be listed. After a few approaches, I decided to implement the "inner" portion as a scalar function, which took a model name, and returned a list of tables associated as XML elements.
The function is listed below. Note that this function assumes all tables are in [dbo] schema. Otherwise, I think you'll need to modify the query to CROSS APPLY over sys.schema.
WITH [GetModelChildren] AS
'U' as "@xtype"
, '[dbo].[' + objname + ']' as "@name"
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL)
WHERE name = @ParentModelName
FOR XML PATH('object'), TYPE
The outer loop would then just a list of all the distinct model names within the database, and then call this function to supply the inner XML. My version looks like this:
SELECT q.value as "@name",
'12' as "@iconsPerRow",
'1' as "@seed",
'0' as "@allowOverlap",
'75' as "@horizontalSpace",
'75' as "@verticalSpace",
'12' as "@maxLabelLength",
'ENGTFS' as "server/@name",
'SQLServer' as "server/@type",
'core' as "server/database/@name",
dbo.GetModelChildren (q.name) as "server/database"
(select distinct name, value from
::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL)
where name like 'IDI_MODEL%'
) AS q
FOR XML PATH('model') , ROOT('models')
I had not done much with XML output before this exercise. The basics are that if you want an XML value with in the current key, the alias should preceded by an @ symbol. Each alias wihtout an @ symbol will be an element, and then you can nest those (e.g. server/@name or server/database ) as needed.
IDI Billing Solutions