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.
|
1 2 3 |
-- What is the properties all databases select * from sys.databases go |
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.
|
1 2 3 4 |
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails. The statement has been terminated. |
I decided to CREATE PROCEDURE that will iterate the databases one by one and execute the sp_helpdb command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
-- -- USP_FIND_DBINFO() - Show me the info baby! -- -- Install in msdb USE [MSDB]; GO -- Delete existing stored procedure IF OBJECT_ID(N'[DBO].[USP_FIND_DBINFO]') > 0 DROP PROCEDURE [DBO].[USP_FIND_DBINFO] GO -- Create new stored procedure (stub) CREATE PROCEDURE [DBO].[USP_FIND_DBINFO] AS SELECT 1; GO -- Alter new stored procedure (full) ALTER PROCEDURE [DBO].[USP_FIND_DBINFO] AS -- Declare local variables DECLARE @STMT NVARCHAR(4000); -- Allocate cursor, return table names DECLARE MYTRG CURSOR FAST_FORWARD FOR SELECT 'sp_helpdb ' + char(39) + d.name + char(39) as STMT FROM master.dbo.sysdatabases d WHERE d.name not in ( 'master' , 'model' , 'tempdb' , 'msdb' , 'pubs' , 'northwind' ) ORDER BY d.name; -- Open cursor OPEN MYTRG; -- Get the first row FETCH NEXT FROM MYTRG INTO @STMT; -- While there is data WHILE (@@FETCH_STATUS = 0) BEGIN -- Show detail database info EXEC sp_executesql @STMT; -- Get the first row FETCH NEXT FROM MYTRG INTO @STMT; END; -- Close the cursor CLOSE MYTRG; -- Release the cursor DEALLOCATE MYTRG; GO |
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.
|
1 |
EXEC [MSDB].[DBO].[USP_FIND_DBINFO] |
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.
