SQL Server 2000 Scripts Corner

17. Save Query Output in SQL Server
There are requirements in various forums that you would like to save the output of the query. There are a number of solutions given. In this code snippet I will give you a method to use this from a script point of view. For the given task I create a generic proc that will facilitate in extracting this data into a file.
Posted On : December 1, 2004	More...	Author : Vinod Kumar

16. Finding size of Table/Indexes in SQL Server 2005
Finding the size tables and indexes occupy in SQL Server 2005 using CTE and DMVs (Dynamic Management Views).
Posted On : August 24, 2004	More...	Author : Vinod Kumar

15. Delete/Truncate all table rows from a database
A frequent question asked is how can we delete all the rows from all the tables in a database. This script will help you achieve the same.
Posted On : July 18, 2004	More...	Author : Vinod Kumar

14. Generate Identity Column Values
Another requirement from the public newsgroup.The table has an unique identifier as an identity column and we need to generate a running number that can be used as an identity column instead of the uniqueidentifier column.
Posted On : April 30, 2004	More...	Author : Vinod Kumar

13. Find if a table has Clustered Index
Simple SQL statement that finds out if a table has got a clustered index or not. Useful for database analysis.
Posted On : October 30, 2003	More...	Author : Vinod Kumar

12. UDF to check if a string is a Valid Roman Number
This UDF allows us to find our if the entered Roman Number string is a valid one or not. This is an interesting code snippet to show the power and usage of UDFs.
Posted On : October 15, 2003	More...	Author : Vinod Kumar

11. Eliminate Non-ASCII Characters
In this script we see how we can extract only ASCII characters from a given string. A useful UDF that can be used in practical applications.
Posted On : October 10, 2003	More...	Author : Vinod Kumar

10. Simulating Oracle ROWNUM in SQL Server 2000
This is one of the most frequent question asked in SQL Server newsgroups. Basically how can we simulate the same behaviour as Oracles ROWNUM functionality. Here is one approach you can use to generate a pseudo row number in SQL Server.
Posted On : September 27, 2003	More...	Author : Vinod Kumar

9. Find Where and When the last backup was taken
How can I find where and when the database last backup was taken. This is an useful script for the DBAs. It shows if it were any network or shared drives with the file name.
Posted On : September 23, 2003	More...	Author : Vinod Kumar

8. How to defragment Indexes in SQL Server 2000
This script allows you to defragment all the indexes present in the current database. The script can be configured to a reduced set.
Posted On : September 23, 2003	More...	Author : Vinod Kumar

7. Find SQL Server - Server level Setting (0.5 KB)
This simple script allows you to view all the SQL Server settings at your server. Can be useful for analysis and resource consumption analysis.
Posted On : September 13, 2003	More...	Author : Vinod Kumar

6. Find when the last backup was taken
How can I find when the database last backup was taken. The script also shows you how you can find the same for Transaction log also.
Posted On : August 29, 2003	More...	Author : Vinod Kumar

5. SQL Version Checker (1.48 KB)
Many times there has been a requirement to check the version or state of a SQL Server from the ISQLW window. Here is an utility SP that helps you find the same easily. Gives you an list of Service Packs / Security packs to be applied also.
Posted On : July 08, 2003	More...	Author : Vinod Kumar

4. Kill Connections (0.6 KB)
This SP can be used to kill all the active connections made to a database. Useful while restoring database. Download the same and try for yourself.
Posted On : July 04, 2003	More...	Author : Vinod Kumar

3. Forming the Database Hierarchy
This article would create an output of Database hierarchy. You can optionally specify a TableName and get the heirarchy for that table alone. This is a good tool for database archietects and database administrators to understand the schema design of their database at the macro level.
Posted On : June 03, 2003	More...	Author : Vinod Kumar

2. Forming a comma Separated String in SQL Server 2000
There are hundreds of T-SQL requirements that lead to innovative and faster coding practices. But the requirement in the industries keep on increasing. Here is one such requirement that Ive been asked by many peers.
Posted On : May 13, 2003	More...	Author : Vinod Kumar

1. How to find Blocking (1.05 KB)
Many times people as me how to debug a blocking at SQL Server. This SP would get you the blocking statements with the culprit who is blocking. A nice little utility SP to have.
Posted On : May 23, 2003	More...	Author : Vinod Kumar

Bangalore .NET User Group (BDotNet), India's Biggest and Active .Net User Group

The MVP Award is given in recognition of a recipient's technical expertise, spirit of community, and their willingness to help their peers by providing reliable and accurate answers to technical questions about Microsoft products & technologies