程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL 2000 to SQL 2005: Where Have All the Old Features Gone?

SQL 2000 to SQL 2005: Where Have All the Old Features Gone?

編輯:關於SqlServer

from SQL-Server-Performance.com By : Boris Baliner
 

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 as to where all the good old features have 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? Can someone pinch me and tell me this is just a bad dream?

The ASPirin industry will profit enormously … that sort of thing.

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

  • Taskpad.
  • Ability to script permissions quickly on stored procedures.
  • Ability to see disk space quickly in database propertIEs.
  • Time of creation of stored procedures.

Sure, if you're connecting to the instance of SQL Server 2005 with Management Studio you have colorful reports and a plethora of professional-looking graphs at your disposal — but what about the majority of us that still have not migrated 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 their typing skills, etc. In the next section I will show how you can still take advantage of the old tools' functionality.



Taskpad Functionality

Don't know about you all, but I really like the Taskpad, and use it all the time. I am as used to it as I am to my old pair of slippers — it fits my needs. And even if it did throw a vague error now and then I forgive it especially since it's gone forever. But how do we get its functionality back?

The General tab in the 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 SQL 2005 tools.

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

select * from sysfiles

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

DBCC SQLPERF (LOGSPACE)

The 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, choose Tasks > Generate Scripts, and 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 the target database.

Click Next and select Stored Procedures only. Next, select which procs you want to script, revIEw your 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 include the following script manually 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 largest headache drug company these days) this option was excluded from the final SQL 2005 RTM.



See Disk Space

If you're like me, 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 information in the report, but until then you can run an undocumented extended stored procedure:

exec xp_fixeddrives




Time of Creation of Stored Procedures

For some reason the time part of the Create Date colum

n in the Summary tab of SQL 2005 is depreciated. Why? I guess someone thought DBAs didn't need it any longer (I'm rolling my eyes). The good news is you can still get this information by querying the sysobjects table:

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



Summary

I've showed 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 are "surprised" when their tools are taken away. All of sudden they have to hammer the nail with the handle of a screwdriver. I hope that the ways to attain old functionality will increase productivity, and that the tools will continue to improve.


 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved