What I’d like to do in this article is to explain something about SQL Server extended properties, why they are so important, and illustrate some ways of working with them, scripting them, and maintaining them.
Why Extended Properties?
Before SQL Server 2000, there was a very awkward problem with SQL Server: It was impossible to attach additional properties to a database object such as a database, schema, table, column, index or so on. Imagine that you wanted to attach a note to a database to provide the current version, and maybe list the previous deployments to bring this database up to the version. You couldn’t do it. These properties are important for documentation; particularly for tables and their associated properties such as columns and indexes because, in these cases, SQL Server doesn’t, and can’t, preserve the CREATE source with all its liberal comments and documentation.
There were then some ways around the problem, of course. Obviously, you could attach information about a database by creating a stored procedure or view to give you what amounted to global constants such as the version number, or the revisions that had taken place. This wouldn’t tackle other problems such as keeping the documentation about the table together with the table; the same applied to indexes, columns and other database objects where the objects source was not retained within the database. Then, as now, we also needed occasionally to attach additional information to columns to apply masks, application-validation information such as regexes, formatting information, caption information or other such things. Above all, it was documentation that was we needed. Then came extended properties.
Extended properties are easy to read, thanks to a useful system view. Sys.extended_properties. However, they are a pain to create, update and delete; they rely on special stored procedures that have a syntax that isn’t at all intuitive for those of us without mutant mental powers. They have a limit of 7,500 characters but are actually stored in a SQL_variant so that DateTime, Approximate numeric, exact numeric, character, Unicode and binary information can be stored in it. Most of us use some sort of tool such as SSMS to maintain this documentation rather than to do it via SQL. The SQL is cumbersome.
Extended Properties within SQL Server are complicated because of the way that SQL Server understands all the components/entities/things of a database. There is no single numeric identifier that will guarantee to uniquely identify a component of a database. Certainly, there is the object_ID of the system catalog views, but then only certain components are considered to be objects. Columns, for example, aren’t objects, and nor are indexes. Parameters aren’t either. Yet these are the very things that need documentation the most. SQL Server relies on a four-part dot-based identification system to reference any component. The name of each type is separated by dots. It will work fine, but because of the ‘uniqueness’ rules of SQL Server, you need to know what each component represents. This means that you also need a list of the types. The problem with this is that the names of database objects types aren’t consistent across the product and SSMS. In the catalog views, ‘Functions’ consist of several object types (TF, FN, IF, FS or FT) as can a ‘Procedure’ (P, PC, RF or X). The information schema catalog uses another different jargon. The diffeences in the terms used by SQL Server and SMO makes things more difficult. .
The four functions or procedures that allow you to maintain the extended properties, sys.fn_listextendedproperty, sp_addextendedproperty, sp_dropextendedproperty and sp_updateextendedproperty, aren’t easy to use. Although you can generally avoid the listing function, you can’t avoid the others easily. You don’t just provide the path to the entity/thing/component, but you have to parse both references into their component levels to provide the parameters to these procedures.
SMO grappled with this very problem and came up with a unique identifier, called the Uniform Resource Name (URN). This does the trick, though it complicates matters by using a subset of an XPath expression. It is a subset of an SMO query string that you can, I’m told, use in SMO to create collections of anything you specify that is an SMO object within the database. The difference is that the URN is a unique reference. With an easily serialised reference such as a URN, it is easy to get hold of a database ‘thing’ and document it by means of an extended property. Once you have any live SMO database object, you can assign a value to an existing extended property or create a new one using its ExtendedProperty value.
SMO has its own terms for the various classes of entities/things that it has in collections. You can easily get a list of them with this PowerShell code.
|
1 2 |
[enum]::GetValues([Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes])| foreach{"$_"} |
You will see that they are differences from the terms used by SQL Server
Editing and creating extended properties in PowerShell.
If you have a serialized version of a unique URN reference to an object, then you have all that you need to instantiate a server object. (Microsoft.SqlServer.Management.SMO.Server) and made a connection with it. You then get any object you wish using the unique reference (URN).You can then read, or write to, any extended property for any component within any database on the server just by specifying Uniform Resource Name (URN) to the extended property of that object. So we can find the value of an MS_Description property on a view by …
|
1 |
($srv.GetSmoObject("Server[@Name='MyInstance']/Database[@Name='MyDatabase']/View[@Name='MyView' and @Schema='MySchema']/ExtendedProperty[@Name='MS_Description']")).Value |
…and you can change an existing value simply by assigning to it. You can create a new property perfectly easily. Here is a PowerShell function that does this for any SMO object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
<# .SYNOPSIS This either creates or alters an extended property on any database component (thing) .DESCRIPTION This uses SMO to check if a particular Extended property exists, and if it does it alters it to the value you supply, otherwise it creates the property with the value you provide .PARAMETER DatabaseObject The SMOobject that represents whatever you wish to attach or edit an extended property to. This is an object in the SMO sense, not the SQL Server sense. .PARAMETER Property The property (e.g. MS_Description) you wish to assign to, or whose value you wish to change. .PARAMETER NewValue The value you wish to assign to it .EXAMPLE $DatabaseName = 'Adventureworks2014' $Schema = 'Sales' $TableName = 'Currency' $Property = 'TestDescription' $NewValue='This is a wonderful table' $TheTable = $srv.Databases[$DatabaseName].Tables.Item($tableName, $Schema) if ((Edit-ExtendedProperty $TheTable $property $newvalue) -ne $TheTable.ExtendedProperties.item($Property).Value) {Write-Error 'Edit-ExtendedProperty failed'} .NOTES Over the years, different objects have been added to the pool of objects that are allowed to have properties. Now if you can think of it, you can probably attach an EP to it. Beware! the documentation is way out-of-date #> function Edit-ExtendedProperty { [OutputType([string])] param ( [Microsoft.SqlServer.Management.Smo.SqlSmoObject]$DatabaseObject, [string]$Property, [string]$NewValue ) $ep = 'Microsoft.SqlServer.Management.Smo.ExtendedProperty' if ($DatabaseObject.ExtendedProperties.item($Property) -eq $null) { $TheEP = new-object $ep($DatabaseObject, $Property, $NewValue) $TheEP.Create() } else { $DatabaseObject.ExtendedProperties.item($Property).Value = $NewValue } $DatabaseObject.ExtendedProperties.item($Property).Value } |
This is convenient. It is much easier than using a SQL Server object_ID, even where we are dealing with a database object. Just for the record, if you happen to have an instantiated database object, and you want to access the extended property of a view with an object_ID of 39671189 you could do this.
|
1 |
($database.Views.ItemById(39671189)).ExtendedProperties['MS_Description'].Value |
You can put extended properties on almost any database ‘thing’. (Long sentence alert!) What do I mean by a ‘thing’? Actually, you can put extended properties on the Database itself, any Object ( which would include Aggregate functions, check constraints, defaults (constraint or stand-alone),foreign key constraints, SQL scalar functions, Assembly (CLR) scalar-functions, ,Assembly (CLR) table-valued functions, SQL inline table-valued functions, SQL Stored Procedures, Assembly (CLR) stored-procedures, Plan guides, primary key constraints, Rules (old-style, stand-alone), Replication-filter-procedures, System base tables, Synonyms or Sequence objects), columns, Parameters, Schemas, Database principals, Assemblies, Types, Indexes, XML schema collections, Message types, Service contracts, Services, Remote service bindings, Routes, Dataspaces (filegroups or partition schemes), Partition functions, Database files or Plan guides. It is quite a list. This is why the URN is so useful for defining the database component that you’re interested in.
Listing out extended properties in SQL
The process of listing out the extended properties in a database is a bit tedious but perfectly reasonable. Basically, you use the sys.extended_properties table and join to other system tables as appropriate to identify precisely what it is you’re attaching a property to. I find this query handy. I haven’t added some of the more obscure database components/things because I’ve never felt the urge to document them and have never found a soul who has. As well as the extended property, I’ve added a reference to the ‘thing’ or object to which the property is attached as well as a dot reference by name. You’d need these if you wanted to use SQL Server’s stored procedures.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
SELECT --objects AND columns CASE WHEN ob.parent_object_id>0 THEN OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.'+OBJECT_NAME(ob.parent_object_id)+'.'+ob.name ELSE OBJECT_SCHEMA_NAME(ob.object_id)+'.'+ob.name END + CASE WHEN ep.minor_id>0 THEN '.'+col.name ELSE '' END AS path, 'schema'+ CASE WHEN ob.parent_object_id>0 THEN '/table'ELSE '' END + '/'+ CASE WHEN ob.type IN ('TF','FN','IF','FS','FT') THEN 'function' WHEN ob.type IN ('P', 'PC','RF','X') then 'procedure' WHEN ob.type IN ('U','IT') THEN 'table' WHEN ob.type='SQ' THEN 'queue' ELSE LOWER(ob.type_desc) end + CASE WHEN col.column_id IS NULL THEN '' ELSE '/column'END AS thing, ep.name,value FROM sys.extended_properties ep inner join sys.objects ob ON ep.major_id=ob.OBJECT_ID AND class=1 LEFT outer join sys.columns col ON ep.major_id=col.Object_id AND class=1 AND ep.minor_id=col.column_id UNION ALL SELECT --indexes OBJECT_SCHEMA_NAME(ob.object_id)+'.'+OBJECT_NAME(ob.object_id)+'.'+ix.name, 'schema/'+ LOWER(ob.type_desc) +'/index', ep.name, value FROM sys.extended_properties ep inner join sys.objects ob ON ep.major_id=ob.OBJECT_ID AND class=7 inner join sys.indexes ix ON ep.major_id=ix.Object_id AND class=7 AND ep.minor_id=ix.index_id UNION ALL SELECT --Parameters OBJECT_SCHEMA_NAME(ob.object_id) + '.'+OBJECT_NAME(ob.object_id)+'.'+par.name, 'schema/'+ LOWER(ob.type_desc) +'/parameter', ep.name,value FROM sys.extended_properties ep inner join sys.objects ob ON ep.major_id=ob.OBJECT_ID AND class=2 inner join sys.parameters par ON ep.major_id=par.Object_id AND class=2 AND ep.minor_id=par.parameter_id UNION all SELECT --schemas sch.name, 'schema', ep.name, value FROM sys.extended_properties ep INNER JOIN sys.schemas sch ON class=3 AND ep.major_id=SCHEMA_ID UNION all --Database SELECT DB_NAME(), '', ep.name,value FROM sys.extended_properties ep where class=0 UNION all--XML Schema Collections SELECT SCHEMA_NAME(SCHEMA_ID)+'.'+XC.name, 'schema/xml_Schema_collection', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.xml_schema_collections xc ON class=10 AND ep.major_id=xml_collection_id UNION all SELECT --Database Files df.name, 'database_file',ep.name,value FROM sys.extended_properties ep INNER JOIN sys.database_files df ON class=22 AND ep.major_id=file_id UNION all SELECT --Data Spaces ds.name,'dataspace', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.data_spaces ds ON class=20 AND ep.major_id=data_space_id UNION ALL SELECT --USER dp.name,'database_principal', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.database_principals dp ON class=4 AND ep.major_id=dp.principal_id UNION ALL SELECT --PARTITION FUNCTION pf.name,'partition_function', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.partition_functions pf ON class=21 AND ep.major_id=pf.function_id UNION ALL SELECT --REMOTE SERVICE BINDING rsb.name,'remote service binding', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.remote_service_bindings rsb ON class=18 AND ep.major_id=rsb.remote_service_binding_id UNION ALL SELECT --Route rt.name,'route', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.routes rt ON class=19 AND ep.major_id=rt.route_id UNION ALL SELECT --Service sv.name COLLATE DATABASE_DEFAULT ,'service', ep.name,value FROM sys.extended_properties ep INNER JOIN sys.services sv ON class=17 AND ep.major_id=sv.service_id UNION ALL SELECT -- 'CONTRACT' svc.name,'service_contract', ep.name,value FROM sys.service_contracts svc INNER JOIN sys.extended_properties ep ON class=16 AND ep.major_id=svc.service_contract_id UNION ALL SELECT -- 'MESSAGE TYPE' smt.name,'message_type', ep.name,value FROM sys.service_message_types smt INNER JOIN sys.extended_properties ep ON class=15 AND ep.major_id=smt.message_type_id UNION ALL SELECT -- 'assembly' asy.name,'assembly', ep.name,value FROM sys.assemblies asy INNER JOIN sys.extended_properties ep ON class=5 AND ep.major_id=asy.assembly_id /*UNION ALL SELECT --'CERTIFICATE' cer.name,'certificate', ep.name,value from sys.certificates cer INNER JOIN sys.extended_properties ep ON class=? AND ep.major_id=cer.certificate_id UNION ALL SELECT --'ASYMMETRIC KEY' amk.name,'asymmetric_key', ep.name,value SELECT * from sys.asymmetric_keys amk INNER JOIN sys.extended_properties ep ON class=? AND ep.major_id=amk.asymmetric_key_id SELECT --'SYMMETRIC KEY' smk.name,'symmetric_key', ep.name,value from sys.symmetric_keys smk INNER JOIN sys.services sv ON class=? AND ep.major_id=smk.symmetric_key_id */ UNION ALL SELECT -- 'PLAN GUIDE' pg.name,'plan_guide', ep.name,value FROM sys.plan_guides pg INNER JOIN sys.extended_properties ep ON class=27 AND ep.major_id=pg.plan_guide_id |
In use, I generally just turn this into a view and select just the ones that I need. This is another reason why I add the ‘thing’ column as well as the ‘path’ column. It makes WHERE clauses easier.
This is what a result looks like…
Scripting out extended properties in PowerShell
PowerShell allows you to script any object, both the create or drop script. Luckily, SMO considers an extended property to be an object so it is pretty easy. For SQL Server, an extended property is just a property, but SMO considers it an object that can be scripted and which even has a URN. There are plenty of ways of gathering up all the ‘things’ that can have an extended property but we’ll get everything that is schema-based. Sure, there are some ‘things’ that can assigned a property but this would just bulk up the routine. Here, I’ll give you the plain script so you can play around with it interactively, but in real life you’d wrap this up in a function. And pass to it, as a parameter, a connected server object and a database name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers $ServerName = 'MyServer' #the full name of your server $DatabaseName='MyDatabase' #the name of your database $Credentials = 'integrated security=True' #your user-id and password if necessary $SMO = 'Microsoft.SqlServer.Management.SMO' # to shorten some of the devotions to libraries $Conn = 'Microsoft.SqlServer.Management.Common.ServerConnection' $SQLConn = 'System.Data.SqlClient.SqlConnection' $connectionString = "Data Source=$serverName;$credentials;pooling=False;multipleactiveresultsets=False;packet size=4096" #this might need to be changed under some circumstances try #making the connection { $srv = new-object "$Smo.Server"(new-object $Conn(new-object $sqlConn($connectionString))) } catch #if it isn't going to happen { "Could not connect to SQL Server instance '$servername': $( $error[0].ToString() + $error[0].InvocationInfo.PositionMessage). The script is aborted" exit - 1 # return -1 if there is an error otherwise 0 } # $schemae = @() #holds the schema collection $children = @() #holds the child objects of each schema. $database = $srv.Databases[$DatabaseName] #instantiate the database object $objectCollection = $database.schemas | Where-Object { $_.Owner -eq 'dbo' } -OutVariable +schemae | foreach{ $_.EnumOwnedObjects() } | Foreach{ $srv.GetSmoObject($_) } -OutVariable +children | Foreach{ $GrandChildren = @() # columns, indexes, triggers, parameters etc $Object = $_; switch ($Object.urn.type) { 'Table' { #add the table and the child objects that can have EPs $table = $Object $grandchildren = $table.Columns | foreach{ $_ } #get the columns $grandchildren += $table.Indexes | foreach{ $_ } #get the indexes $grandchildren += $table.Triggers | foreach{ $_ }#get the triggers $grandchildren += $table.Checks | foreach{ $_ } } 'View' { #add the Views and the child objects that can have EPs $View = $Object $grandchildren = $View.Columns | foreach{ $_ } #get the columns $grandchildren += $View.Indexes | foreach{ $_ }#get the indexes $grandchildren += $View.Triggers | foreach{ $_ }#get the triggers } 'StoredProcedure' #add the SPs and the child objects that can have EPs { $grandchildren = $Object.Parameters | foreach{ $_ }#get the parameters } 'UserDefinedFunction' { $UserDefinedFunction = $Object $grandchildren = $UserDefinedFunction.Columns | foreach{ $_ } #get the columns $grandchildren += $UserDefinedFunction.Checks | foreach{ $_ } $grandchildren = $UserDefinedFunction.Parameters | foreach{ $_ }#get the parameters } 'Queue' { $queue = $Object.EventNotifications }#get the event notifications }$grandchildren #put all these columns, indexes, triggers, parameters etc in the collection } $objectCollection += $schemae #add these schemae to the collection of objects $objectCollection += $children #and the schema-based objects (e.g. tables, functions views) $objectCollection += $database #and the database itself $TheBuildScript=$objectCollection | #now we just take all the extended properties and foreach {$_.ExtendedProperties.GetEnumerator()}|foreach{$_.Script()} #script them out |
This would give you something like this- in the case of AdventureWorks…(just an extract!)
|
1 2 3 4 5 6 7 8 9 |
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores (including store addresses) that sell Adventure Works Cycles products to consumers.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N 'VIEW',@level1name=N'vStoreWithAddresses' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores (including store contacts) that sell Adventure Works Cycles products to consumers.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N' VIEW',@level1name=N'vStoreWithContacts' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores (including demographics) that sell Adventure Works Cycles products to consumers.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VI EW',@level1name=N'vStoreWithDemographics' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Collection of XML schemas for the Demographics column in the Sales.Store table.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'XML SCHEMA COLLECTION',@level1name=N'StoreSurveySchemaCollection' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AdventureWorks 2014 Sample OLTP Database' |
Which would look like this in SSMS after a touch of formatting
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Stores (including store addresses) that sell Adventure Works Cycles products to consumers.', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VIEW', @level1name = N'vStoreWithAddresses'; EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Stores (including store contacts) that sell Adventure Works Cycles products to consumers.', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N' VIEW',@level1name=N'vStoreWithContacts'; EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Stores (including demographics) that sell Adventure Works Cycles products to consumers.', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VI EW',@level1name=N'vStoreWithDemographics'; EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Collection of XML schemas for the Demographics column in the Sales.Store table.', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'XML SCHEMA COLLECTION',@level1name=N'StoreSurveySchemaCollection'; EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'AdventureWorks 2014 Sample OLTP Database'; |
The first thing I’d want to experiment with is to change that last part of the script
|
1 2 |
$TheBuildScript=$objectCollection | #now we just take all the extended properties and foreach {$_.ExtendedProperties.GetEnumerator()}|foreach{$_.Script()} #script them out |
To this, which will produce the SQL scripts to drop all the extended properties.
|
1 2 3 4 5 |
$option=new-object 'Microsoft.SqlServer.Management.Smo.ScriptingOptions' $option.ScriptDrops=$true $TheBuildScript=$objectCollection | #now we just take all the extended properties and produce drop statements for them foreach {$_.ExtendedProperties.GetEnumerator()}|foreach{$_.Script($option)} #drop them all |
…like this. (just a sample, of course)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VIEW', @level1name = N'vSalesPerson'; EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VIEW', @level1name = N'vSalesPersonSalesByFiscalYears'; EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VIEW', @level1name = N'vStoreWithAddresses'; EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VIEW', @level1name = N'vStoreWithContacts'; EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'VIEW', @level1name = N'vStoreWithDemographics'; EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'Sales', @level1type = N'XML SCHEMA COLLECTION', @level1name = N'StoreSurveySchemaCollection'; EXEC sys.sp_dropextendedproperty @name = N'MS_Description'; |
Getting Extended property information.
So far we have managed to get the create and drop SQL Scripts from PowerShell. Because we are, in effect, iterating through all the extended properties to do this, we can actually perform the drop on them directly, but I’d be very careful with this! Just to finish this section, I’d like to show how we’d get a listing of all the ‘thing’s in a database that can have extended properties, including the URN so we can alter any extended property that we want, and see what hasn’t been documented yet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers Import-module psYAML $ServerName = 'MyServer' #the full name of your server $Credentials = 'integrated security=True' #or your user-id and password $SMO = 'Microsoft.SqlServer.Management.SMO' # to shorten some of the devotions to libraries $Conn = 'Microsoft.SqlServer.Management.Common.ServerConnection' $SQLConn = 'System.Data.SqlClient.SqlConnection' $connectionString = "Data Source=$serverName;$credentials;pooling=False;multipleactiveresultsets=False;packet size=4096" #this might need to be changed under some circumstances try #making the connection { $srv = new-object "$Smo.Server"(new-object $Conn(new-object $sqlConn($connectionString))) } catch #if it isn't going to happen { "Could not connect to SQL Server instance '$servername': $( $error[0].ToString() + $error[0].InvocationInfo.PositionMessage). The script is aborted" exit - 1 # return -1 if there is an error otherwise 0 } # $Database.Schemas['dbo'].enu $schemae = @() $children = @() $database = $srv.Databases[$DatabaseName] $objectCollection = $database.schemas | Where-Object { $_.Owner -eq 'dbo' } -OutVariable +schemae | foreach{ $_.EnumOwnedObjects() } | Foreach{ $srv.GetSmoObject($_) } -OutVariable +children | Foreach{ $GrandChildren = @() # columns, indexes, triggers, parameters etc $Object = $_; switch ($Object.urn.type) { 'Table' { #add the table and the child objects that can have EPs $table = $Object $grandchildren = $table.Columns | foreach{ $_ } #get the columns $grandchildren += $table.Indexes | foreach{ $_ } #get the indexes $grandchildren += $table.Triggers | foreach{ $_ }#get the triggers $grandchildren += $table.Checks | foreach{ $_ } } 'View' { #add the Views and the child objects that can have EPs $View = $Object $grandchildren = $View.Columns | foreach{ $_ } #get the columns $grandchildren += $View.Indexes | foreach{ $_ }#get the indexes $grandchildren += $View.Triggers | foreach{ $_ }#get the triggers } 'StoredProcedure' #add the SPs and the child objects that can have EPs { $grandchildren = $Object.Parameters | foreach{ $_ }#get the parameters } 'UserDefinedFunction' { $UserDefinedFunction = $Object $grandchildren = $UserDefinedFunction.Columns | foreach{ $_ } #get the columns $grandchildren += $UserDefinedFunction.Checks | foreach{ $_ } $grandchildren = $UserDefinedFunction.Parameters | foreach{ $_ }#get the parameters } 'Queue' { $queue = $Object.EventNotifications }#get the event notifications }$grandchildren #put all these columns, indexes, triggers, parameters etc in the collection } $objectCollection += $schemae #add these schemae to the collection of objects $objectCollection += $children #and the schema-based objects (e.g. tables, functions views) $objectCollection += $database #and the database itself $TheObjectsAndEPs = $objectCollection | Select Name, URN, Extendedproperties | foreach { $Thing = ''; $path = ''; $MyURN = $_.URN; if ($MyUrn.XPathExpression.Length -gt 3) { #it is a property of an object $path = '.' + "$($MyUrn.XPathExpression[3].FixedProperties.Name)".Trim(''''); $Thing = "/$($MyUrn.XPathExpression[3].Name)"; }; if ($MyUrn.XPathExpression.Length -gt 2) { #need to specify schema. is it schema-based? if ($MyUrn.XPathExpression[2].FixedProperties.Schema -eq $null) { #then it isn't schema-based $path = "$($MyUrn.XPathExpression[2].FixedProperties.Name)".Trim('''') + $path; $Thing = "$($MyUrn.XPathExpression[2].Name)$Thing"; } else #it is schema-based { $path = "$($MyUrn.XPathExpression[2].FixedProperties.Schema)".Trim('''') + '.' + "$($MyUrn.XPathExpression[2].FixedProperties.Name)".Trim('''') + $path $Thing = "schema/$($MyUrn.XPathExpression[2].Name)$Thing"; } }; if ($MyUrn.XPathExpression.Length -eq 2) { #it is the database itself $path = "$($MyUrn.XPathExpression[1].FixedProperties.Name)".Trim('''') $Thing = "database" } if ($_.ExtendedProperties.Name -ne $null) { #then there are properties @{ $Path = @{ 'Name' = $_.Name; 'Urn' = $_.Urn.Value; 'Thing' = $Thing; 'EP' = $_.ExtendedProperties.GetEnumerator() | select Name, Value; } } } else { @{ $Path = @{ 'Name' = $_.Name; 'Urn' = $_.Urn.Value; 'Thing' = $Thing; } } } } ConvertTo-YAML $TheObjectsAndEPs . |
I’m using YAML to serialise all this information in order to archive it. By using this, I can see what isn’t and what is documented. Here is a short sample. As with quite a lot of SMO, this type of script is very slow with a database of any considerable size.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
- Sales.Currency: Thing: 'schema/Table' Name: 'Currency' EP: - Name: 'Description' Value: 'This table passeth all understanding.' - Name: 'MS_Description' Value: 'Lookup table containing standard ISO currencies.' - Name: 'Dependencies' Value: ‘This information needs filling in’ Urn: > Server[@Name='MYSERVER']/Database[@Name='Adventureworks2014']/Table[@Name='C urrency' and @Schema='Sales'] - Sales.StoreSurveySchemaCollection: Thing: 'schema/XmlSchemaCollection' Name: 'StoreSurveySchemaCollection' EP: Name: 'MS_Description' Value: 'Collection of XML schemas for the Demographics column in the Sales.Store table.' Urn: > Server[@Name='MYSERVER']/Database[@Name='AdventureWorks2014']/XmlSchemaColle ction[@Name='StoreSurveySchemaCollection' and @Schema='Sales'] - AdventureWorks2014: Thing: 'database' Name: 'AdventureWorks2014' EP: Name: 'MS_Description' Value: 'AdventureWorks 2014 Sample OLTP Database' Urn: 'Server[@Name=''MYSERVER'']/Database[@Name=''AdventureWorks2014'']' |
Conclusions
Extended properties can be habit-forming once you’ve got confidence with them. When all the aspects and entities of a database are documented, it makes the maintenance effort a lot easier. That index for example. You can save that half-minute spent puzzling out the how and why. You can use automated processes to attach dependency information to tables, functions and procedures. You can preserve table or index header comments in EPs, or even store the commented table-sources at build or deployment time. Extended properties are a great solution, and once you’re familiar with them, you’ll suddenly find the problems they solve.
Load comments