Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL 2000 to SQL 2005: Where have all the old features gone?

By Boris Baliner, (first published: 2006/03/16)

Introduction

As more DBAs across the planet begin using SQL 2005 Tools, but still manage SQL 2000 servers with them, I suspect there will be lots of muffled moaning and wondering where have all the good old features gone. Although Management Studio has some very nice long-awaited features, some of the good old stuff just isn’t there.

Where are my tried and true tools, such as taskpad? Where’s the IF EXISTS DROP option when I script out the stored procedures? Could someone pinch me and tell me this is just a bad dream?

The aspirin industry will profit enormously from that sort of thing.

To name a few good old pals that have all but disappeared into obscurity:

·       Taskpad

·        Ability to quickly script permissions on stored procedures

·        Ability to quickly see disk space in database properties

·        Time of creation of stored procedures

Sure, if you're connecting to an instance of SQL Server 2005 with Management Studio you get colorful reports and plethora of professional-looking graphs at your disposal, but what about the majority of us that still did not migrate our servers to SQL 2005, but already upgraded the tools?

The good news is this will tend to convert many GUI DBAs into hardened command-line pros, improve they're typing skills, etc. In the next section I will show how to still take advantage of the old tools functionality.

Taskpad functionality

I don't know about you all, but I really like the Taskpad and use it all the time. I am used to it like to an old slipper; it fits my needs. And even if it did throw a vague error now and then I forgive it now that it's done….forever. But how can we get its functionality back?

The General tab in Database section is now in database properties under the same heading.

Maintenance section-like information can be found by querying the backupset table in msdb:

select max(backup_start_date) from backupset
where database_name = ‘my_db'

Note: Database options, Number of Users, Date Created and Owner can still be found in database properties in the SQL 2005 tools.

Space allocated section info can be found by running this T-SQL:

select * from sysfiles

or if you just need to find the space used by you log, execute:

DBCC SQLPERF (LOGSPACE)

Table Info tab

I don't use this one very often, but you can get similar functionality by running:

Exec sp_spaceused ‘your_table_name'

To script multiple stored procedures including permissions:

Right-click the database->Tasks->Generate Scripts, pick your database. Set Include object Level Permissions to True. Note: If you set the Include if NOT EXISTS option to true, the script will not create the stored procedure if it already exists on target database.

Click Next, and select Stored Procedures only. Next select which procs you want to script, review you final options and click Finish.

Unfortunately, if you want to drop/recreate the procedures if they exist on the target server, you will need to manually include the following script in the beginning of each procedure:

IF EXISTS (SELECT name FROM sysobjects 
            WHERE name = 'your_proc_name' AND type = 'P')
 DROP PROCEDURE 'your_proc_name'
GO

This one is truly beyond me, for reasons known only to Microsoft and the CEO of Bayer (or whoever is the biggest headache drug company these days) this option was excluded from final SQL 2005 RTM.

Check disk space

If you're like I am, you're used to clicking on database properties, and the ellipsis in order to see the free disk space on the server. In SQL Server 2005 you can get this in the report, but until then you can run undocumented extended stored procedure:

exec xp_fixeddrives

The result would look something like this:

Time of creation of stored procedures:

For some reason the time part of the Create Date column in the Summary tab of SQL 2005 is depreciated. Why? I guess someone thought DBAs don't need it any longer (rolling eyes). The good news is you can still get this information by querying the sysobjects table, like:

Select crdate as DateCreated
 From dbo.sysobjects 
 where name = 'your_proc_name'
 

Note: make sure NOT to specify the owner, as in dbo.your_proc_name, but simply the procedure name. The result is:

Summary

I've shown you here how to get by with available SQL 2005 Tools until you upgrade your servers to SQL 2005 Server edition. People get used to their favorite ways to get the job done, and sometimes get “surprised” when their tools get taken away, and now they have to hammer the nail with the handle of a screwdriver. Hopefully the ways to attain old functionality will increase productivity, and hopefully the tools will continue to improve.

 

Total article views: 35460 | Views in the last 30 days: 14
 
Related Articles
FORUM

Scripting Stored Procedures

Scripting Stored Procedures

FORUM

Script all Stored procs for all databases on a server

script all stored procs for all databases on a server

SCRIPT

Generate Drop / Create Script for views, stored Procedures

This Script generates drop and create scripts for views or stored procedures.

SCRIPT

consolidated script generator for multiple stored procedures

Consolidated script generator for multiple stored procedures (existing and/or newly created) in MS S...

FORUM

Stored Procedure & 'other' database

How to have a Stored Procedure work on another database

Tags
other    
sqlservercentral    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones