Finding Database Info

If you are a production Database Administrator (DBA), you might be lucky and have all your servers on the same revision of SQL Server. This is a very uncommon situation since many third party vendors do not keep up with Microsoft releases or your company might not find the return on investment (ROI) for having the lastest software.

My company falls into the later category. We have some business applications that are bound to SQL Server 2000. Since it is not broken, the company is not willing to fix it. The good thing is most of my databases are 2008 R2.

So, how does this relate to my article?

Well, I would normally get information by selecting from sys.databases. See code snippet below. But this view does not exist in this old version. See Jeremy Kadlec MS SQL Tips article on System Information in SQL Server 2000 versus 2005.

I know that the sp_helpdb command is available in both the older and newer versions. However, it does not complete execution due to broken ownership chains. I ended up seeing the following error message.

I decided to CREATE PROCEDURE that will iterate the databases one by one and execute the sp_helpdb command.

Wow, we definitely have a lot to talk about.

First, a stored procedure is a database object that is associated with stored (compiled) code. Like most Data Definition Language (DDL) constructs, a user define stored procedure has three operations associated with it: CREATE PROCEDURE, ALTER PROCEDURE and DROP PROCEDURE. I used all three constructs to remove the object if it exists, create a stub object, and replace the object with the fully coded logic.

Second, the stored procedure uses a cursor to traverse the list of database names. A CURSOR follows the following design pattern: declare the cursor, open the cursor, select the first row, while there is data select more rows, close the cursor and de-allocate the cursor.

Use cursors with caution. They are very useful for administrative procedures that do not involve large data sets or one-time adhoc program logic. They are bad for high frequency tasks since they are usually slower than their relational algebra equivalents.

Last but not least, we are dynamically creating a SQL statement to execute. It is stored in the variable named @STMT. I am using the sp_executesql system stored procedure to execute the dynamic code.

While you think this might fix our issues, we still have ownership issues with the old databases. I used the EXEC sp_changedbowner ‘sa’ while using the database that needs an owner.

A quick call to the our new stored procedure producing the following results.

To recap, I was able to introduce both STORED PROCEDURE and CURSORS to you. I will cover both these topics in more detail in the future. The over all program we created will return database information to you regardless if you are using SQL Server 2000 all the way to SQL Server 2012.

Next time, I will be introducing a Dynamic Management View (DMV) to get information on a table. This is useful when you are given a new database to manage and want to discover usage patterns.

Related posts

Leave a Comment