2005 Find Table without Clustered Index – Find Table with no Primary Key

USE AdventureWorks ----Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO

Get a List of Fixed Hard Drive and Free Space on Server

If I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. 

EXEC master..xp_fixeddrives

Get Directory Structure using Extended Stored Procedure xp_dirtree

Well, there is one undocumented stored procedure exists which can do the same. However, please be vary to use any undocumented procedures.

xp_dirtree 'C:\Windows'


Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.

Here is the quick script which will insert the data into the temptable and retrieve from the same.

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Windows'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;

Get Server Version and Additional Info

It is quite common to get the SQL Server version details from following query.

SELECT @@VERSION VersionInfo



Recently I have been using following SP to get version details as it also provides me few more information about the server where the SQL Server is installed.

EXEC xp_msver





I like to use the second one but again that is my preference.