I'm working on a project that leverages database metadata. To date I've just queried the database catalog views (e.g. sys.tables) but I know this will be "easier" with SMO if I'm willing to add the dependency.
What are the advantages to SMO over metadata queries? Any "gotchas" (e.g. needing to have additional SQL libraries on my local machine just for the luxury of SMO).
sys.tables and others are not guaranteed to stay unchanged between different versions of SQL Server so next version might break your code. SMO will be updated with next version of SQL Server and the interfaces should not change so SMO is the more robust solution.
Greets Flo
The metadata queries will require you to join many different sys.tables with specific queries relating to what you need to script.
An in-depth knowledge of these relationships is require to use sys.tables.
SMO is much simpler, and has collections representing your database structure.
Also, SMO has a Dependency Walker, which will allow you to query the dependencies of database objects.