We need to pull a lot of metadata from the database. Specifically we need to get (on Sql Server 2000, 2005, & 2008):
- All tables & views - their schema, name, description, and if they are a system or user object.
- All table/view columns - their name, description, data type, and, if it is a foreign key, the table.column of the primary key it links to.
- All stored procedures - their name, description, and return type, and if they are a system or user object.
- All procedure parameters - their name, description, data type, data length, default (if any), and direction.
We have selects to do this but they are very complex and slow. We recently had an Oracle systems engineer help us on this same topic on Oracle and he had us create a temporary table that had all this info, we then read the rows in the table to get all
this info and then dropped the table. It was a gigantic performance improvement.
So... Any suggestions on the best way to pull out this info? We grab it all at once so a temporary table is fine. Individual selects for each step is fine too. Suggestions on how best to do this are very appreciated.
thanks - dave
Very funny video -
Reporting as a Metaphor
View Complete Post