Database Management

MSSQL Database Version

For information on the MSSQL database version, open an SQL query in the Management console and enter the following command:

ELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Table Sizes in MSSQL Database

For information on table sizes of an MSSQL database, go to the Management console and open the following script:

DECLARE @TableSpace table (
TableName varchar(255),
Rows int,
DataSpaceUsed int,
IndexSpaceUsed int
)
DECLARE
@Rows int,
@DataSpaceUsed int,
@IndexSpaceUsed int,
@TableName varchar(255)
DECLARE Table_Cursor CURSOR FOR
SELECT user_name(o.uid) + '.' + o.name AS table_name
FROM dbo.sysobjects o, dbo.sysindexes i
WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
AND i.id = o.id
AND i.indid < 2
AND o.name NOT LIKE N'#%'
AND xtype = 'U'
ORDER BY 1
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
EXEC sp_MStablespace @TableName
update @TableSpace set TableName = @TableName where TableName is null
FETCH NEXT FROM Table_Cursor INTO @TableName
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
SELECT TableName, cast(DataSpaceUsed+IndexSpaceUsed as varchar) + ' KB' AS TotalSpace,
cast(DataSpaceUsed as varchar) + ' KB' as DataSpaceUsed,
cast(IndexSpaceUsed as varchar) + ' KB' as IndexSpaceUsed, Rows
FROM @TableSpace
ORDER BY DataSpaceUsed+IndexSpaceUsed DESC
--select cast(Total as varchar) + ' KB' as Total
--from (select sum(DataSpaceUsed+IndexSpaceUsed) as Total from @TableSpace) as tab