Extracting Metadata - Retrieving Identity Columns
(Page 19 of 22 )
Identity columns are SQL Server columns that auto-increment to ensure that each value is unique. These are often used to guarantee a unique primary key for tables. The type of each identity column is one of the integer types. Each identity column also has a start or seed value and an increment value. These default to int, 1, and 1. This metadata extraction tool just extracts which columns are identity columns from the type name returned by sp_help for the column.
Retrieving Extended Properties
The SQL function that returns extended properties can be a bit nonintuitive to use. Ultimately, it’s giving you a recordset of name-value pairs. To get these for anything in the database, it uses a rather convoluted concept of levels. SQL Server refers to these three levels as level0, level1, and level2 objects, but it’s easier to think of them as owner, object, and item. Objects can be tables, stored procedures, and so on, and items can be columns, parameters, and so on. For each level there’s a type and a name. You have to specify both the type and the name for levels above what you’re requesting. For the level you’re requesting, you can just specify the type to retrieve all extended properties of that type. If the first parameter is specified, it indicates the specific property to be returned. Thus, you’d retrieve the Caption parameter of the CompanyName column using the following:
SELECT * FROM ::fn_listextendedproperty('Caption', 'User', 'dbo', 'Table', 'Customers', 'Column', 'CompanyName')
As an example of retrieving higher-level data based on partial parameters, the following returns all of the extended properties for tables in the database:
SELECT * FROM ::fn_listextendedproperty(null, 'User',
'dbo', 'Table', null, null, null)
Note that I specified the user as 'dbo' at the highest level, but only the type at the current level ('Table').
Introducing Careful Naming I settled on the phrase careful naming for this section because of my lack of comfort calling it anal naming, but it means about the same thing. Names within your database can carry the information you need to build metadata if you do the following:
- Accept the premise that a name carries meaning.
- Allow that meaning to be overloaded with detailed rules.
- Commit to being very careful, almost fanatical, in your naming.
NOTE: Careful naming is most important for stored procedures.
Do your stored procedures already have names that indicate what they’re doing and what they’re doing it to? Most databases I’ve seen are awfully close to careful naming, except for two important aspects. They don’t use explicit clause delimiters, and they always seem to include a handful of exceptions. These exceptions really mess things up, and careful naming is really a commitment to follow a set of rules absolutely. It’s a 100 percent thing. If you rely on it for determining intent during metadata extraction and then do something in an unexpected way, you’ll generally have an extra or missing object, method, or property.
If careful naming doesn’t sound like a good fit for your workgroup, another option is to store the task and identify information for each stored procedure in extended properties of the stored procedures or external freeform XML that can be merged with your metadata. The benefit of careful naming is that everyone can see the intent quickly and find what they want even after stored procedures proliferate in your database. Chapter 7 shows how to use code generation to build stored procedures, and this makes it easy to follow careful naming rules. However, your DBA may already be quite good at making and following detailed rules.
Implementing Naming Delimiters
If you change your stored procedure naming to a careful naming scheme, the most important feature you’ll introduce is delimiters. Humans do very well with Pascal or camel casing because we gather meaning even when rules are broken.
Capitalization is a hard way to delimit meaning to a computer because humans are imperfect in applying it. You have no problem understanding the intent of these samples:
nwdCustomersSelect ' nonambiguous
nwdOrderDetailsSelectSpecialCase ' potentially ambiguous
versus the intent of these:
nwd_Customers_Select ' nonambiguous
nwd_OrderDetails_Select_SpecialCase ' never ambiguous
If you really hate underscores, you can work with casing. You’ll struggle with ensuring clarity to both the computer and humans, especially with multipart names such as OrderDetails. You’ll also need to avoid table names that contain your selected keywords such as Insert, Select, Update, Delete, or Process.
Implementing Naming Rules
The specific naming rules you use aren’t essential. They could be different from mine, as long as you can articulate and follow them. The key things you’re looking for are the intent (category) of the stored procedure, the subset of your database where it’s applicable if you have a large database, and specifics that allow you to make a meaningful unique name for your middle-tier classes.
The naming rules I use for stored procedures consist of four portions, or clauses separated by underscores. Careful naming relies on being able to non-ambiguously separate the clauses of the name—a task made easy by the underscore delimiters:
< subset abbrev >_< logical table name >_< type >_< specifier >
This format breaks down into clauses as follows:
subset abbrev: Designates major subsets of the database functionality, such as acc for accounting. These abbreviations should be three or possibly four characters. You can use a prefix such as utl (for utility) or prv (for private) to indicate those stored procedures that shouldn’t be exposed in metadata. This is a common way to organize categories of stored procedures, and you can use this information to organize your business objects into multiple assemblies if desired.
logical table name: Set of columns that generally map to a single business class and one or more physical database tables. You can also think of a logical table as any unique combination of columns. A single physical table might map to several logical tables that each containing a different subset of columns. You’ll also use different logical tables when you have joins producing different recordset structures. Each logical table needs a unique name—the table name of the physical table returned when there’s a one-to-one mapping.
type: Type of the stored procedure. This will be Select, SetSelect, Insert, Update, Delete, Process, Report, or Internal. Report stored procedures are generally Select stored procedures but are used differently (or not used) in metadata.
specifier: Additional information that also makes the stored procedure name unique. Depending on how you build your stored procedures and how complex your database is, there’s a good chance the other rules won’t be sufficient to generate unique naming. For example, you’ll frequently have specific variations of select stored procedures with different search criteria such as this:
nwd_ Customers_Select_ByAccountNumber
or this:
nwd_ Customers_Select_ByNameLastFirst
This last section of a specifier is optional and will probably appear in some, but not all, of your stored procedure names.
If you like to suffix your stored procedures with a consistent suffix such as _sp, you can also do that. The metadata extraction tools ignore this suffix.
If you put these rules together, the intent of each of the stored procedures in your database will be clear, and you can avoid the additional hassle of maintaining information about the intent of your stored procedures. If you aren’t doing this now and have a significant database with stored procedures calling other stored procedures or other applications using your database, changing your stored procedure naming is a nearly impossible job. In that case, you probably want to maintain this information in freeform data or extended properties.
 This is from Code Generation in Microsoft .NET, by Kathleen Dollard (Apress, ISBN 1590591372). Check it out at your favorite bookstore today. Buy this book now. |
Next: Creating Freeform Metadata >>
More Database Articles
More By Apress Publishing
/ 18 
