Monday, November 2. 2009
Does your company have an Architectural Roadmap?
Friday, October 9. 2009
Sql Server Metadata
Being a developer these days is tough. Ok, it's never been easy to be a good developer, but now the scope of technology in which you need to have some level of competence is staggering. Particularly in consulting & contracting you have to be ready to discuss topics ranging from HTML to SOA, design patterns, language specifics, browser foibles, winforms, Windows services, web services, and even some familiarity with networking is important. But when it's time to code something that accomplishes a task, it's back to the language and the dbms.
I'm often surprised at how little many senior developers know about the dbms side of the equation. Even after a reasonable level of sql coding skill is acquired, an understanding of how to mine information from the dbms metadata would seem like a logical next step, but one seldom taken. And it can make your life so much easier!
For example, let's say you have a table that has a field named "RhumbaStyleID". Thanks to Dancing With the Stars, the application you're updating for DanceCo Inc now supports Waltzes, the Foxtrot, Modern Interpretive Dance, and your personal favorite, Belly Dancing. There are plans for other styles of dance, too. Now the RhumbaStyleID needs to be changed to DanceStyleID. The database has 14 Gigazillion stored procs and tables, so where do you start? Well, how about discovering which stored procedures include the RhumbaStyleID field? In Transact Sql, you could do something like this...
select
object_name(object_id) from sys.sql_modules where definition like '%RhumbaStyleID%' order by object_name(object_id)Pretty painless. How about which tables have a RhumbaStyleID column?
select Table_Catalog + '.' + table_schema + '.' + table_name from INFORMATION_SCHEMA.COLUMNS where column_name = 'RhumbaStyleID'
Or maybe this one (which will return tables plus views and table-type UDF's)...
select
object_name(object_id) from sys.columns where name = 'RhumbaStyleID'Woo hoo! Now we're getting somewhere.
Here's another situation. In a particularly painful troubleshooting exercise, you have discovered a foreign key constraint was disabled, and this allowed some very subtle data problems. You want to make sure there aren't any more constraints in this condition. Here's a quick-n-dirty for that...
select Name, OBJECT_NAME(parent_object_id) as Parent, schema_name(schema_id)as SchemaName, OBJECT_NAME(referenced_object_id) as ReferencedObject, delete_referential_action_desc DeleteAction, update_referential_action_desc UpdateAction, is_disabled Disabled from sys.foreign_keys where is_disabled = 1
See the possibilities? I'm not even scratching the surface here. There are a dozens of these "System Views" in sql server 2005 & 2008. Some provide catalog information others reveal dynamic information. And the same or variations of these have existed since way-back in Sql Server history. So even if you're lost in Sql 2000 Land (or the Mordor known as Sql 7.0), you'll find most of the same functionality under different names. Here's a link to a map that correlates Sql 2000 system tables to 2005 system views.
http://technet.microsoft.com/en-us/library/ms187997(SQL.90).aspx
For a full-color poster-size pdf download of an ER diagram displaying the system views for 2008:
And here's the Sql 2008 System Views reference page:
http://technet.microsoft.com/en-us/library/ms177862.aspx
Enjoy!
Steve Willoughby, Sr. Technical Architect, Logical Advantage
Friday, September 18. 2009
Recovering from the Recovery
This past week we saw the Federal Reserve Chair, Ben Bernanke, as well as countless CEOs and politicians publicly declare the greatest recession since the Great Depression is officially over. I suppose if you are fortunate enough to still have a paycheck coming your way and house over your head, you can let out a collective sigh of relief. Unfortunately for those who fall in the other category, the so-called "experts" are predicting a jobless recovery which means job creation may be slow in coming.
So now that the recession is over, what does that mean to IT executives?
There is no doubt that the recession of all recessions has made the past couple of years difficult for IT. Many organizations were forced to cut budgets, slash their IT workforce, and forced to do more with less. Companies have pursued cost cutting across the IT landscape, including training, infrastructure and enterprise applications. While some have cut development work, including enhancements and new projects, there is a collective belief that strategic investments in IT can lead to business process improvement thus creating opportunities to grow and transform the business. It doesn't take a rocket scientist to recognize that that those who have invested in IT during this uncertain time, are now poised to deliver a solid competitive advantage.
A recent Gartner survey showed that 86% of organizations are making strategic IT investments where there is a potential for competitive advantage and transformation, despite the economic downturn, because many of their competitors are using this an opportunity to take advantage of new technologies and get ready for a return to growth.
Does that sound like your company? If not, then you should hope and pray it isn't your competition.
Steve Nolan, Managing Partner


