In the antecedent commodity we looked at how you can add snippets of affidavit to a database’s metadata application continued properties. As you accept seen, this is not complicated, and uses alone one arrangement stored action – pr_addextendedproperty. About the basal techniques acclimated to dispense continued properties, while simple, Can become acutely arduous back you appetite to attending at or abstract all (or a ample selection) of the continued backdrop which you accept added to a database.
So this commodity will appearance you how to:
Before proceeding to the affair of this article, it is reasonable to ask area continued backdrop are stored. The conspicuously simple and reasonable acknowledgment is that they can be activate in the arrangement metadata appearance sys.extended_properties. This appearance looks commodity like the following:
The acceptable account about this appearance is that it is abundantly simple. The bad account is that the way that the continued backdrop (the “name” and “value” )are stored does not chronicle calmly to the advice anesthetized in application the stored action “pr_addextendedproperty”. Indeed, the stored procedure’s break of elements into a bureaucracy of levels does not map calmly to the “major_id” and “minor_id” of the appearance either.
So it is capital to realise that what you are attractive at are IDs of objects. Globally, all table, view, stored action and action altar – and all the altar such as columns, indexes, constraints and ambit which depend on these altar can be discerned by the major_id, which is the object_id in sys.objects. For columns, the minor_id is the column_id. Aloof about aggregate abroad needs to be affiliated to addition ID – sys.data_spaces for filegroups, etc. (I won’t account it all out here, as the cipher to appear has all of this). This is fabricated easier to accept by the “class_desc”, which indicates (reasonably clearly) which arrangement table contains the major_id.
SQL Server Management Studio will calligraphy out continued backdrop as follows:
So, now that you apperceive area to look, and accept a basal butt of what you are attractive at, it is time to move on to accepting this metadata out of the database.
To activate with, acceptable to the arrangement action “fn_listextendedproperty”. As there are abounding acceptable descriptions accessible on the web to explain it, I will not afflict this commodity with an all-embracing rehash, but do admonish you to attending at it, as it absolutely has its uses as a quick way of advertisement out continued properties.
The action requires seven parameters:
1. Acreage name2. Level 0 commodity type3. Level 0 commodity name4. Level 1 commodity type5. Level 1 commodity name6. Level 2 commodity type
So it uses the aforementioned bureaucracy to abstract continued backdrop as you bare to accept to actualize them.A few quick examples should accomplish this clearer:
Here is a action example:
This is how to get the continued backdrop for all tables in a schema:
This is how to get the continued backdrop for all the angle in a schema:
This is how to get the continued backdrop for all the columns in a table:
This is how to get the continued backdrop for a specific cavalcade in a table:
All in all, I am abiding you will accede that it is a quick and reliable way to abstract continued properties.
So actuality is an archetype of how to extend the use of the action to abstract the continued backdrop for added than one table and for a ambit of classifications:
CROSS APPLY fn_listextendedproperty (NULL, ‘schema’, ‘Sales’, ‘table’, MD.TABLE_NAME, NULL, NULL) EP WHERE EP.name IN (‘TestCreate’, ‘Overview’)
This admission lets you baddest elements from the INFORMATION_SCHEMA table(s) and assorted elements from fn_listextendedproperty – all application accepted WHERE clauses.
I will not accord added examples, but achievement that this will acknowledgment to point you in the appropriate direction. Addition ambush to accessing continued backdrop for assorted commodity types is to use assorted CROSS APPLY queries, like the one apparent aloft and UNION them to accommodate a distinct achievement table.
However, I am activity to advance that to accomplishment all your time and accomplishment spent documenting a database added fully, that you are best bypassing “fn_listextendedproperty” altogether, and activity beeline to the metadata arrangement views, which acquiesce a added absolute (and I feel added coherent) way of application this metadata. So from actuality on I will be application arrangement metadata to admission database metadata.
Here again, I will accommodate a set of cipher snippets, so that you can archetype and adhesive those that you acquisition useful, alike if you don’t charge all of them.
To booty a thoroughly applied archetype as a starting point, accept that you accept agilely accurate your database objects, and now ambition to add all this capital advice into a Word certificate to affect your superiors or audience with some alarming abstruse documentation. After any retyping, of course.Let’s activate with the simplest of all continued backdrop – the database. The afterward cipher atom will achievement all continued backdrop for the database itself:
The achievement will be commodity like:
For a schema, the cipher atom and achievement could be commodity like this:
WHERE SEP.class_desc = N’SCHEMA’
And the achievement is:
And for a table:
And for a column:
You will accept noticed that these four examples chase the four levels of the continued backdrop hierarchy:
OK, so now that the basal account are clear, actuality are (without the outputs), the cipher snippets to abstract the continued backdrop from all the altar that we looked at in the aboriginal article. These will abide by attractive at all the continued backdrop of tables:
FROM sys.extended_properties SEPINNER JOIN sys.procedures SPRON SEP.major_id = SPR.object_id INNER JOIN sys.schemas SCHON SPR.schema_id = SCH.schema_id INNER JOIN sys.parameters ON SEP.major_id = sys.parameters.object_id AND SEP.minor_id = sys.parameters.parameter_id
WHERE SEP.class_desc = N’PARAMETER’
Now you accept apparent both how to abstract continued backdrop from the arrangement metadata angle as able-bodied as from fn_listextendedproperty, you are apparently apprehensive which one is best to use. The acknowledgment is artlessly – you choose.
However, these are some advantages to authoritative the added accomplishment to use the arrangement angle to admission your continued properties. These include:
Anyway, I am absolutely not adage that fn_listextendedproperty is not acutely useful, aloof that by compassionate how continued backdrop are handled by SQL Server you can accept how best you appetite to administer them.
Once you can abstract all the continued backdrop from your databases, the catechism is again – what are you activity to do with them? I brainstorm that nine times out of ten the acknowledgment will be: actualize affidavit in MS Word. The set of T-SQL snippets accustomed aloft will advice you abstract the continued properties, but you will again accept to archetype and adhesive them into a document, and finalise the formatting. This can be a little laborious, so I am including with this commodity a baby MS Word macro, captivated in a Word Arrangement file, which will abstract and architecture continued backdrop for you.
To use it:
Assuming all goes well, you should see commodity like this:
Just to explain my acumen as to why I chose to use VBA to do this, and not address a aggregate .NET application:
You may disagree with this approach, in which case feel chargeless to carbon and blanket in .NET – and column on SQL Server Central!
So, I achievement that you will accept fun with continued backdrop – and that you activate this description useful.
The abutting commodity will accord with basal aspects of industrial-strength database affidavit application Continued Backdrop – in added words how to adapt and annul hundreds of comments at a time for a ambit of objects.
Free Invoice Template For Word 8 Why You Should Not Go To Free Invoice Template For Word 8 – free invoice template for word 2003
| Encouraged for you to my blog site, within this time I’m going to teach you about keyword. And from now on, this is the first graphic: