Getting the Disk Usage Statistics for all Tables in a Database

I know, there are standard reports and other GUI tools for doing this task for SQL Server 2005 or later but what about when you need to extract such information from a SQL Server 2000 instance or when you just want to write some code? 🙂   Get the disk usage statistics using the undocumented … Read more…

How To Get Basic SQL Server Instance Info (T-SQL Script)

With the below T-SQL script, you can get basic SQL Server instance info such as: Full instance name SQL Server version Edition Collation Number of databases Product level (i.e. SP-level) …and more   The T-SQL Script The script uses the built-in SQL Server function SERVERPROPERTY. SELECT SERVERPROPERTY(‘ServerName’) AS FullInstanceName, REPLACE(SUBSTRING(@@version,0,CHARINDEX(‘-‘,@@version)),’Microsoft ‘,”) as FullSQLVersion, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion, SERVERPROPERTY(‘ProductLevel’) … Read more…

T-SQL Tip: Retrieving Database File Sizes

The following script generates T-SQL statements that when ran, they return file size information for all the user databases on a SQL Server instance. –Script that generates T-SQL providing size information for all database files on a SQL Server InstanceSELECT ‘SELECT ”’+[name]+”’ as DBName,cast(f.name as varchar(25)) as DBFileName,f.Filename as PhysicalFileName,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB … Read more…

Retrieving Log Space Information within a SQL Server Instance – The Stored Procedure!

In yesterday’s post, we saw how we can retrieve log space information for all the databases within a SQL Server instance using the command DBCC sqlperf(logspace). Today we will refine the process even more by creating a stored procedure that returns log space information for a given database! –******************************–CREATING THE STORED PROCEDURE–****************************** –Select the database … Read more…

How to retrieve size information for all the tables in a SQL Server Database

In an older post, I described how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure “sp_MSforeachtable”. Another common task is when you want to retrieve size information for all the tables in a database. Again, by using “sp_MSforeachtable“, you can easily do that … Read more…

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed…

In this article, we will be discussing about the following error message in SQL Server and how to easily resolve it: Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed…   About this Error Message If you ever encounter issues when trying to create, add, or edit steps for a … Read more…

Using Unicode in SQL Server

In this article, we will be discussing about using Unicode in SQL Server.   Introduction Unicode is the standard used in the computing industry for encoding and representing any text in the most written languages (…). SQL Server supports Unicode, thus allowing the easy storage and manipulation of data in the most languages.   Example … Read more…