Utility SPs for developers / administrators alike. These scripts will work in MS SQL 70. It may require some modifications for the next release. I will post updates as and when i modify them. These scripts address several issues with the existing system SPs. The enterprise manager "Current Activity" window doesn't really help in a busy production environment owing to its usage of temporary tables to display the data etc. It does not provide powerful search capabilities also. I think i've got most of these taken care of in these SPs. Please leave the header, details in these Sps for clarity sakes & for others to benefit by them. Hope you find these useful & wish that it simplifies some of your daily chores! Please send any questions, suggestions, bugs to .
Thanks for checking this out!
Click here to download the SQL scripts.
1) This script shows how to get details about an orphan process in SQL70.
2) This SP can be used to search for a string in the last input command sent to SQL Server from a user connection. The search can be done based on SPIDs or username.
3) This SP demonstrates how to generate INSERT statements for a table.
4) Just a wrapper SP with a short name that calls my sp_who_ex SP & the blocking locks SP from Kalen's website.
5) A SP to view the blocking locks in a database. Taken from Kalen's InsideSQLServer website.
6) This is the modified version of the system SP to fix the arithmetic overflow errors.
7) This SP is used to generate a default template with DROP, CREATE, GRANT for SPs, Views & Triggers. The template also contains a comment that can use the Visual SourceSafe keyword expansion feature to maintain history of the changes.
8) This SP can be used to list the i/o buffer of a specific SPID. The SPIDs can be specified as a list or the username can be specified.
9) This SP allows powerful search capabilities & ordering features for the processes using SQL Server. This does not use temporary tables & hence works better than the EM current activity window.
10) How to create files, write to files, delete files from T-SQL using DOS commands?
11) This SP provides a detailed report on tables & indexes similar to the EM Tables & Indexes page. Provides filtering capabilities, eliminating DBCC output messages etc. Demonstrates other techniques to manipulate files from SQL Server.
12) This script contains the tables required for the sp_lock_ex tables. This data needs to be updated for each version of SQL Server maybe. Am working on elminating this table.
13) This SP can be used to view locking information in the system. Allows various searching capabilities that is not available in EM & this one doesn't use temporary tables.
14) A simple example that demonstrates how to use the Script method of SQLDMO from T-SQL.
15) How to the AUTOSTART SQL Server setting using SQL-DMO. This example can be extended to change any registry setting for SQL Server, SQLServerAgent, and SQLMail services.
16) This utility SP was taken from a KB article on how to monitor blocking in SQL70. You can find the KB article here.
17) A script to automate the transfer of standard logins from one server to another with the passwords. This one uses a distributed query against the source server.
18) This administrative script demonstrates several powerful programming techniques. This can be used to load shipped logs & it uses the Log Shipping tables to read the parameters. The main reason that this script exists is that Log Shipping allows only restores in NORECOVERY mode only & this SP provides more options.
19) Modified sp_change_user_login that corrects the working of the AUTO_FIX parameter.
20) This adminsitrative script demonstrates some techniques to RESTORE databases & logs from timestamped files. This can be used to automate restores on a standby / development server.
21) This utility stored procedure generates several reports about the transaction log, the virtual log files that comprise the log & their sizes. This SP uses the undocumented DBCC LOGINFO to obtain the details. This SP is designed to work under SQL70/2000.
22) This utility stored procedure can be used to obtain a report of the data filegroups & the transaction log. This uses the undocumented DBCC SHOWFILESTATS statement & the documented DBCC SQLPERF( LOGSPACE ) statement.
|
